How to declare a variable in a PostgreSQL query

Question

How do I declare a variable for use in a PostgreSQL 8.3 query?

In MS SQL Server I can do this:

DECLARE @myvar INT
SET @myvar = 5

SELECT *
FROM somewhere
WHERE something = @myvar

How do I do the same in PostgreSQL? According to the documentation variables are declared simply as "name type;", but this gives me a syntax error:

myvar INTEGER;

Could someone give me an example of the correct syntax?

Answer 1

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

Answer 2

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;

Answer 3

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.

Answer 4

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

Answer 5

It depends on your client.

However, if you're using the psql client, then you can use the following:

my_db=> \set myvar 5
my_db=> SELECT :myvar  + 1 AS my_var_plus_1;
 my_var_plus_1 
---------------
             6

If you are using text variables you need to quote.

\set myvar 'sometextvalue'
select * from sometable where name = :'myvar';

Answer 6

Using a Temp Table outside of pl/PgSQL

Outside of using pl/pgsql or other pl/* language as suggested, this is the only other possibility I could think of.

begin;
select 5::int as var into temp table myvar;
select *
  from somewhere s, myvar v
 where s.something = v.var;
commit;

Answer 7

This solution is based on the one proposed by fei0x but it has the advantages that there is no need to join the value list of constants in the query and constants can be easily listed at the start of the query. It also works in recursive queries.

Basically, every constant is a single-value table declared in a WITH clause which can then be called anywhere in the remaining part of the query.

• Basic example with two constants:

WITH
    constant_1_str AS (VALUES ('Hello World')),
    constant_2_int AS (VALUES (100))
SELECT *
FROM some_table
WHERE table_column = (table constant_1_str)
LIMIT (table constant_2_int)

Alternatively you can use SELECT * FROM constant_name instead of TABLE constant_name which might not be valid for other query languages different to postgresql.

Answer 8

I want to propose an improvement to @DarioBarrionuevo's answer, to make it simpler leveraging temporary tables.

DO $$
    DECLARE myvar integer = 5;
BEGIN
    CREATE TEMP TABLE tmp_table ON COMMIT DROP AS
        -- put here your query with variables:
        SELECT * 
        FROM yourtable
        WHERE id = myvar;
END $$;

SELECT * FROM tmp_table;

Answer 9

Here is an example using PREPARE statements. You still can't use ?, but you can use $n notation:

PREPARE foo(integer) AS
    SELECT  *
    FROM    somewhere
    WHERE   something = $1;
EXECUTE foo(5);
DEALLOCATE foo;

Answer 10

True, there is no vivid and unambiguous way to declare a single-value variable, what you can do is

with myVar as (select "any value really")

then, to get access to the value stored in this construction, you do

(select * from myVar)

for example

with var as (select 123)    
... where id = (select * from var)

Answer 11

You may resort to tool special features. Like for DBeaver own proprietary syntax:

@set name = 'me'
SELECT :name;
SELECT ${name};

DELETE FROM book b
WHERE b.author_id IN (SELECT a.id FROM author AS a WHERE a.name = :name);

Answer 12

In DBeaver you can use parameters in queries just like you can from code, so this will work:

SELECT *
FROM somewhere
WHERE something = :myvar

When you run the query DBeaver will ask you for the value for :myvar and run the query.