How to declare a variable in a PostgreSQL query
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, myconstantsWHERE something = var1 OR something_else = var2;
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 usersWHERE master_login = (SELECT login FROM master_user) AND (SELECT registration_date FROM master_user) > ...;
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.