Declare row type variable in PL/pgSQL

Question

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 $$; 

Answer 1

get only 2-3 columns instead of all columns

One way: use a record variable:

DO $$ DECLARE    _rec record; BEGIN SELECT INTO _rec             id, name, surname FROM t WHERE id = ?; END $$; 

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 $$ DECLARE    _id int;    _name text;    _surname text; BEGIN SELECT INTO _id, _name, _surname              id,  name,  surname FROM t WHERE id = ?; END $$; 

---

As for your first example: %ROWTYPE is just noise in Postgres. The documentation:

(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 $$ DECLARE    my_data t;  -- table name serves as type name, too.  BEGIN    SELECT INTO my_data  * FROM t WHERE id = ?; END $$;