Geek Answers Handbook

Declare a string type variable in PL / pgSQL

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

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

I'm right?

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

I.e

 DO $$ DECLARE my_data <WHAT HERE??>; BEGIN SELECT id,name,surname FROM t INTO my_data WHERE id = ?; END $$;
+6
source share
1 answer

get only 2-3 columns instead of all columns

One way: use the record variable:

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

Note that a structure of type record undefined has not yet been assigned. Thus, you cannot reference columns (fields) before doing this.

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. Documentation :

(Since each table has a related composite type with the same name, in fact it doesn't matter in PostgreSQL whether you write %ROWTYPE or not. But a form with %ROWTYPE 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 $$;
+11
source

More articles:


All Articles