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 feature available in PostgreSQL. But, you can do it only in pl/PgSQL (or other pl/*), but not in plain SQL.

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

Note: In this one exception is WITH () query which will work as a variable or even tuple of variables. It also allows you to return a table of temporary values.

To master SQL statements, queries and become proficient in SQL queries, enroll in an industry-recognized SQL Online Course.