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?

SQL has not variables. They are typically provided by the SQL client - which tool do you use to run the script? — , Sep 19 '19 at 06:05

score 11 · Accepted Answer · answered Sep 19 '19 at 08:00

You are confused on several levels.

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.
There are variables in PL/pgSQL, which are defined in the DECLARE section, but there are no variables in SQL.
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;

score 3 · Answer 2 · answered Sep 19 '19 at 07:59

3

The good structure is like this :

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

answered Sep 19 '19 at 07:59

Jaisus

1,019
5
14

How to declare a variable in postgres script?

2 Answers2