SELECT multiple rows and columns into a record variable
There are no table variables in PL/pgSQL - at least up to Postgres 14, and likely never.
Use temporary tables:
Or substitute with CTEs (or just subqueries in simple cases) for the local scope of a single query. A "single query" can encompass multiple commands when using (data-modifying) CTEs. That would be most efficient:
Or combine cursors with loops (consider the example under FNC - Function):
But it's typically simpler and more efficient to use the implicit cursor of a FOR
loop:
interpost
Updated on April 12, 2022Comments
-
interpost about 2 years
In a plpgsql function, how can multiple rows and columns be selected into a record variable?
For example, I would like to
SELECT
multiple instances of two columns (yearinteger
andvalue
) into a record variable (yearvalues
).*EDIT - the following code is just part of a longer function, I need the variable
yearvalues
to contain multiple rows and columns from a table from which I can create further variables fromCREATE OR REPLACE FUNCTION fn_function () RETURNS TABLE () AS $$ DECLARE year c.year%TYPE; value c.value%TYPE; yearvalues record; BEGIN FOR yearvalues IN SELECT c.year, c.value FROM c LOOP END LOOP; -- creation of additional variables from the yearvalues variable END; $$ LANGUAGE plpgsql;
-
Craig Ringer almost 10 years... and when you run this function, what happens? (Always show exact error message text).
-
Vivek S. almost 10 yearsread this and go through PosgreSQL Resources in SO
-
a_horse_with_no_name almost 10 yearsIf the function returns a table, why not use a
sql
function that simply returns the result of theselect
? Or areturn query
in the PL/pgSQL function?
-
-
interpost almost 10 yearsthank you for describing the various options; I think cursors are what I am looking for.
-
vdegenne over 6 yearswhat do you mean "(at least up to v9.4)" ? It's a bit confusing statement. Does that mean there are table variables in newer versions ?
-
Erwin Brandstetter over 6 years@user544262772: How would I know that at the time of writing? There are still no table variables in version 10, and I don't expect that to change in future versions.
-
vdegenne over 6 years@ErwinBrandstetter I have a question, please can you enter this room ? chat.stackoverflow.com/rooms/154260/… this message will be deleted