SELECT multiple rows and columns into a record variable

Question

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 and value) 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 from

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

... and when you run this function, what happens? (Always show exact error message text). — Craig Ringer
– Craig Ringer, Commented Jul 25, 2014 at 6:50
If the function returns a table, why not use a sql function that simply returns the result of the select? Or a return query in the PL/pgSQL function? — user330315
– user330315, Commented Jul 25, 2014 at 7:37

Erwin Brandstetter · Accepted Answer · 2022-04-11 19:00:31Z

16

There are no table variables in PL/pgSQL - at least up to Postgres 14, and likely never.

Use temporary tables:

Select from a table variable

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:

Switching from FOR loops in plpgsql to set-based SQL commands

Or combine cursors with loops (consider the example under FNC - Function):

Window Functions or Common Table Expressions: count previous rows within range

But it's typically simpler and more efficient to use the implicit cursor of a FOR loop:

edited Apr 11, 2022 at 19:00

answered Jul 25, 2014 at 20:56

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

interpost Over a year ago

thank you for describing the various options; I think cursors are what I am looking for.

vdegenne Over a year ago

what 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 a year ago

@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 a year ago

@ErwinBrandstetter I have a question, please can you enter this room ? chat.stackoverflow.com/rooms/154260/… this message will be deleted

Collectives™ on Stack Overflow

SELECT multiple rows and columns into a record variable

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related