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

Question

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?

Answer 1

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);

• About temporary tables and autovacuum

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

• PostgreSQL loops outside functions. Is that possible?

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:

• Passing user id to PostgreSQL triggers

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

• Is there a way to define a named constant in a PostgreSQL query?

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