[SOLVED] Declare a variable in a PostgreSQL query

Declare a variable in a PostgreSQL query

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?

Solution

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