How to declare a variable in a PostgreSQL query

Question

This probably sounds like a really stupid question, but 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

I don't know what's the error maybe it's not related to the declaration, try this:

myvar INTEGER DEFAULT  5;

SELECT *
FROM somewhere
WHERE something = myvar

Answer 2

There is no such feature in PostgreSQL. You can do it only in pl/PgSQL (or other pl/*), but not in plain SQL.

Answer 3

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;