Declare row type variable in PL/pgSQL
29,672
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 $$;
Author by
Vyacheslav
Updated on July 21, 2022Comments
-
Vyacheslav almost 2 years
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 $$;