12

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;
interpost
  • 267
  • 1
  • 6
  • 16
  • ... and when you run this function, what happens? (Always show exact error message text). – Craig Ringer Jul 25 '14 at 06:50
  • read [this](http://www.day32.com/MySQL/Meetup/Presentations/postgresql_stored_procedures.pdf) and go through [PosgreSQL Resources in SO](http://stackoverflow.com/tags/postgresql/info) – Vivek S. Jul 25 '14 at 07:19
  • 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? –  Jul 25 '14 at 07:37

1 Answers1

13

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • thank you for describing the various options; I think cursors are what I am looking for. – interpost Jul 26 '14 at 07:49
  • 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 ? – vdegenne Sep 12 '17 at 14:31
  • @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. – Erwin Brandstetter Sep 12 '17 at 15:30
  • @ErwinBrandstetter I have a question, please can you enter this room ? https://chat.stackoverflow.com/rooms/154260/a-question-about-postgresql-temporary-tables *this message will be deleted* – vdegenne Sep 12 '17 at 18:07