Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

postgresql - How to test my ad-hoc SQL with parameters in Postgres query window

In Microsoft SQL Server, to test something like this in the query window:

select * from Users where LastName = @lastname

I can add this before the command:

declare @lastname varchar(16)
set @lastname = 'Troy'

But in PostgreSQL, I cannot find a similar way to do so. It seems the only thing I can do is to replace the parameter name directly with its value. It gets hard when the ad-hoc query gets complicated and the same parameter gets used several times. Is there a way?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Various options.

Provide parameters in a CTE to have "variables" in pure SQL:

WITH var(lastname) AS (SELECT 'Troy'::varchar(16))
SELECT *
FROM   users, var v
WHERE  lastname = v.lastname;

This works for any query.
Since the CTE var holds a single row it is safe to append it with a CROSS JOIN at the end of the FROM clause - actually the short form with appending it after a comma may be best because explicit join syntax binds before commas. The additional table alias v is optional to further shorten the syntax.

OR cheaper without CTE. BTW, why varchar(16)? Just use text:

SELECT *
FROM   users
JOIN  (SELECT 'Troy'::text) var(lastname) USING (lastname)
WHERE  lastname = var.lastname;

Or use a temporary table to play a similar role for all queries within the same session. Temp tables die with the end of the session.

CREATE TEMP TABLE var AS
SELECT text 'Troy' AS lastname;

ANALYZE var;  -- temp tables are not covered by autovacuum

SELECT * FROM users JOIN var USING (lastname);

Or you can use DO statements like @Houari supplied or like demonstrated here:

Note that you cannot return values from DO statements. (You can use RAISE ... though.) And you cannot use SELECT without target in plpgsql - the default procedural language in a DO statement. Replace SELECT with PERFORM to throw away results.

Or you can use customized options, which you can set in postgresql.conf to be visible globally.

Or set in your session to be visible for the duration of the session and only in the same session:

SET my.lastname = 'Troy';

The variable name must include a dot. You are limited to text as data type this way, but any data type can be represented as text ...

You can use current_setting('my.lastname') as value expression. Cast if you need. For example: current_setting('my.json_var')::json ...

Or use SET LOCAL for the effect to only last for the current transaction. See:

Or you can use tiny IMMUTABLE functions as global persisted variables that only privileged users can manipulate. See:

Or when working with psql as client, use the set or gset meta-commands and variable substitution.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...