SELECT multiple rows and columns into a record variable

Question

In the plpgsql function, how can I select multiple rows and columns in 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 part of a longer function, I need the yearvalues variable to contain several rows and columns from a table from which I can create additional 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; 

Answer 1

No table variables in plpgsql (at least until v10).

You can use a temporary table:

• Select from table variable

You can replace CTEs (or even subqueries in simple cases) for the local area of ​​a single request. A “single request” may include several commands (in modifying CTE data). This would be most effective:

• Migrating from FOR loops in plpgsql to set-based SQL commands

Or combine cursors with loops (consider an example in FNC - Function):

• Window functions or general table expressions: count previous rows within a range