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

in microsoft sql server, test in query window:

select * users lastname = @lastname 

i can add before command:

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

but in postgresql, cannot find similar way so. seems thing can replace parameter name directly value. gets hard when ad-hoc query gets complicated , same parameter gets used several times. there way?

provide parameters in cte have "variables" in pure sql:

with var(lastname) (select 'troy'::varchar(16)) select *   users, var v  lastname = v.lastname;

this works any query.
since cte var holds single row safe append cross join @ end of clause - short form appending after comma best here, because explicit join syntax binds before commas. additional table alias v optional further shorten syntax.

or use temporary table serve similar role all queries within same session. temp tables die end of session.

create temp table var select 'troy'::varchar(16) lastname;  analyze var;  --  temp tables not covered autovacuum!  select *   users, var v  lastname = v.lastname; 

• about temp tables , autovacuum

or can use do statements @houari supplied or demonstrated here:

• postgresql loops outside functions. possible?

note cannot return values do statements. (you can use raise ... though.) , cannot use select without target in plpgsql code (the default procedural language in do statement. replace select perform throw away results.

or can use customized options, can set in postgresql.conf visible globally.

or set in session visible duration of session (and within same session):

set my.lastname = 'troy'; 

the variable name must include dot. limited text data type way, data type can represented text ...

you can use current_setting('my.lastname') value expression. cast if need. example: current_setting('my.json_var')::json ...

or use set local effect last current transaction.

related answer @craig:

• passing user id postgresql triggers

or can use tiny simple immutable functions global persisted variables privileged users can manipulate:

• is there way define named constant in postgresql query?

this related answer has similar list of options.

psql

has \set meta-command , provides variable substitution in client.

pgadmin

the pgadmin query window offers extension pgscript.