How to declare a variable in a PostgreSQL query
You could also try this in PLPGSQL:
DO $$
DECLARE myvar integer;
BEGIN
SELECT 5 INTO myvar;
DROP TABLE IF EXISTS tmp_table;
CREATE TABLE tmp_table AS
SELECT * FROM yourtable WHERE id = myvar;
END $$;
SELECT * FROM tmp_table;
The above requires Postgres 9.0 or later.
I accomplished the same goal by using a WITH
clause, it's nowhere near as elegant but can do the same thing. Though for this example it's really overkill. I also don't particularly recommend this.
WITH myconstants (var1, var2) as (
values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
OR something_else = var2;
Dynamic Config Settings
you can "abuse" dynamic config settings for this:
-- choose some prefix that is unlikely to be used by postgres
set session my.vars.id = '1';
select *
from person
where id = current_setting('my.vars.id')::int;
Config settings are always varchar values, so you need to cast them to the correct data type when using them. This works with any SQL client whereas \set
only works in psql
The above requires Postgres 9.2 or later.
For previous versions, the variable had to be declared in postgresql.conf
prior to being used, so it limited its usability somewhat. Actually not the variable completely, but the config "class" which is essentially the prefix. But once the prefix was defined, any variable could be used without changing postgresql.conf
There is no such feature in PostgreSQL. You can do it only in pl/PgSQL (or other pl/*), but not in plain SQL.
An exception is WITH ()
query which can work as a variable, or even tuple
of variables. It allows you to return a table of temporary values.
WITH master_user AS (
SELECT
login,
registration_date
FROM users
WHERE ...
)
SELECT *
FROM users
WHERE master_login = (SELECT login
FROM master_user)
AND (SELECT registration_date
FROM master_user) > ...;