postgresqltypesplpgsqldeclareselect-into

Declare row type variable in PL/pgSQL


As I found SELECT * FROM t INTO my_data; works only if:

DO $$
DECLARE
my_data t%ROWTYPE;
BEGIN
SELECT * FROM t INTO my_data WHERE id = ?;
END $$;

Am I right?

If I want to get only 2-3 columns instead of all columns. How can I define my_data?

That is,

DO $$
DECLARE
my_data <WHAT HERE??>;
BEGIN
SELECT id,name,surname FROM t INTO my_data WHERE id = ?;
END $$;

Solution

  • get only 2-3 columns instead of all columns

    One way: use a record variable:

    DO
    $do$
    DECLARE
       _rec record;
    BEGIN
       SELECT INTO _rec
              id, name, surname
       FROM   t
       WHERE  id = ?;
    
       -- do something with it ...
    END
    $do$;
    

    Note that the structure of a record type is undefined until assigned. So you cannot reference columns (fields) before you do that.

    Another way: assign multiple scalar variables:

    DO
    $do$
    DECLARE
       _id int;
       _name text;
       _surname text;
    BEGIN
       SELECT INTO _id, _name, _surname
              id,  name,  surname
       FROM   t
       WHERE  id = ?;
    END
    $do$;
    

    %ROWTYPE is just noise in Postgres. The manual:

    (Since every table has an associated composite type of the same name, it actually does not matter in PostgreSQL whether you write %ROWTYPE or not. But the form with %ROWTYPE is more portable.)

    So:

    DO
    $do$
    DECLARE
       my_data t;  -- table name serves as type name, too. 
    BEGIN
       SELECT INTO my_data * FROM t WHERE id = ?;
    END
    $do$;