How to declare a variable in postgres script?

Question

I am fairly new to Postgres and I cannot believe how difficult I am finding just to declare a variable. I did come across other SO posts, but none of them helped in my situation. All I want is to write the a script like below in postgres:

declare @age int = 10;
select * from person p where p.age > @age;

Based on the SO post here, I tried:

DO
$$
 DECLARE
 overTheAgeOf int := 15;
 BEGIN
 select *
 from person
 where age > overTheAgeOf;
 END
$$;

This gives me error: [42601] ERROR: query has no destination for result data

Then I tried returning the result of the script:

return (select *
 from person
 where age > overTheAgeOf);

That gave me another error: ERROR: RETURN cannot have a parameter in function returning void

How do declare a variable and use it in script(s) that follows?

• user330315: SQL has not variables. They are typically provided by the SQL client - which tool do you use to run the script?
• haku: I was using DataGrip.

Answer 1

You are confused on several levels.

1. There is the query language SQL, and there is the procedural language PL/pgSQL. The only connection is that

   • you can run SQL statements from PL/pgSQL code

   • you can have PL/pgSQL code in the body of the SQL statements DO and CREATE FUNCTION/PROCEDURE.

2. There are variables in PL/pgSQL, which are defined in the DECLARE section, but there are no variables in SQL.

3. DO statements cannot return any values.

If you want to use PL/pgSQL variables, and you want to return values, you'll have to use a function. An example:

CREATE FUNCTION getpersons() RETURNS SETOF person
 LANGUAGE plpgsql AS
$$DECLARE
 overTheAgeOf int := 15;
BEGIN
 RETURN QUERY
 SELECT *
 FROM person
 WHERE age > overTheAgeOf;
END;$$;
SELECT getpersons();

There is the alternative of using variables on the client. With the psql client, you could use:

\set overTheAgeOf 15
SELECT *
FROM person
WHERE age > :overTheAgeOf;

Answer 2

The good structure is like this :

DO
 LANGUAGE plpgsql $$
DECLARE
 variable int := 0;
BEGIN
 -- your code
 raise notice '%', variable::varchar;
END;
$$;