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?

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;