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.