🐘SQL functions: 3 solutions to stash and reuse rows🌰

Stashing data in a postgres function

Sometime when you write a PLpgSQL function you need to stash data to reuse it later in a same function.
The 3 main ways are:

1. Use a temporary table
2. Use an array of records
3. Use a cursor

Each has its pros and cons, let’s quickly take a look with some sample code.

1. use a temporary table

The most “SQL” way to stash data is probably to create a temporary table in the function, use it, and then drop it.

➕Very SQL-y (portable, easy to move in/out of function, easy to debug)
➖Creating a table, even temporary, can be a big deal. You need permissions, the function must be volatile, and it can have side effects on your session (if you forget to delete the temp table for instance, or when parallelizing).

CREATE OR REPLACE FUNCTION public.demo_stashing__temp_table( ) 
RETURNS TABLE (topic_id bigint, problem_id bigint , image_id bigint, wkt text) 
LANGUAGE PLPGSQL STRICT 
VOLATILE AS $fun1$
/* Demo function to stash rows using a temp table.
        
    Warning:
        Creating temp table has side effects (need permission, affect the rest of your session, function need to be volatile)
*/
DECLARE
    _r record ;  
BEGIN
    --
    -- pretend we get some data from a query and stash it in a temporary table
    CREATE TEMPORARY TABLE temp_stash AS 
    SELECT fake_table.* 
    FROM (
        VALUES (123, 1, -1, 'POINT EMPTY  ')
             , (456, 2, -2, 'POLYGON EMPTY'))  as fake_table(_topic_id, _problem_id , _image_id, _wkt ) ; 
    --
    -- Show the stashed data 
    FOR _r IN
        SELECT * FROM temp_stash  
    LOOP
        RAISE NOTICE '_r : %', _r ; 
    END LOOP;
    --
    -- reuse the stash data (here, just returning it)
    RETURN QUERY 
        SELECT _topic_id::bigint, _problem_id::bigint , _image_id::bigint, _wkt::text 
        FROM temp_stash ; 
    --
    -- drop temporary table
    DROP TABLE IF EXISTS temp_stash ; 
    --
END ; 
$fun1$ ; 

-- TEST : 
  SELECT *
  FROM public.demo_stashing__temp_table( ) ;
    --> return the 2 rows

2. use an array of row variable

If you don’t need to stash too much data, you can aggregate the rows you want to stash into a SQL array variable

➕Rather lightweight, contained in the function.
➖Won’t scale, a lot of boiler plate code to declare naming and type of each column of the rows.

CREATE OR REPLACE FUNCTION public.demo_stashing__array( ) 
RETURNS TABLE (topic_id bigint, problem_id bigint , image_id bigint, wkt text) 
LANGUAGE PLPGSQL STRICT IMMUTABLE
AS $fun1$
/* Demo function to stash rows using an array
        
    Warning:
        only for small amount of rows: creating a temp table will be more efficient, but more bothersome.
*/
DECLARE
    _r record ; 
BEGIN
    --
    -- pretend we get some data from a query and we want to stash it in an array
    SELECT array_agg( ROW(fake_table.topic_id::bigint, fake_table.problem_id::bigint, fake_table.image_id::bigint, fake_table.wkt::text)) as array_of_rows INTO _r  
    FROM (
        VALUES (123, 1, -1, 'POINT EMPTY  ')
             , (456, 2, -2, 'POLYGON EMPTY'))  as fake_table(topic_id, problem_id , image_id, wkt ) ; 
    --
    -- display the stashed data 
    RAISE NOTICE '_r : %', _r ; 
    --
    -- reuse the stash data (here, just returning it)
    RETURN QUERY 
        SELECT u.topic_id, u.problem_id, u.image_id, u.wkt
        FROM UNNEST (_r.array_of_rows) as u(topic_id bigint, problem_id bigint , image_id bigint, wkt text) ; 
    --
END ; 
$fun1$ ; 

-- TEST : 
  SELECT * 
  FROM public.demo_stashing__array( ) ;
      --> returns the 2 rows 

3. use a SQL cursor

If you really want something that is closer to imperative programming, you can create cursor that holds the results of a query and loop on it. It gives great control, but is the least SQL-y way to do things.

➕Contained in the function, a lot of control
➖Not super efficient, limitations on typing.

CREATE OR REPLACE FUNCTION public.demo_stashing__cursor( ) 
RETURNS setof record 
LANGUAGE PLPGSQL  STRICT IMMUTABLE AS $fun1$
/* Demo function to stash rows using a cursor
        
    Warning:
        Innefficient, makes typing more complicated
*/
DECLARE
    _r record ; 
    demo_cursor refcursor;  
BEGIN
    --
    -- pretend we get soem data from a query and stash it in a CURSOR
    OPEN demo_cursor FOR 
            SELECT fake_table._topic_id::bigint,  fake_table._problem_id::bigint, fake_table._image_id::bigint, fake_table._wkt::text
            FROM (
                    VALUES (123, 1, -1, 'POINT EMPTY  ')
                         , (456, 2, -2, 'POLYGON EMPTY')
                 )  as fake_table(_topic_id, _problem_id , _image_id, _wkt ) ; 
    --
    -- Show the stashed data 
    LOOP    
        fetch demo_cursor into _r; 
        exit when not found;
        RAISE NOTICE '_r : %', _r ; 
    END LOOP;
    --
    -- reuse the stash data (here, just returning it)
    MOVE BACKWARD ALL FROM demo_cursor ; -- reset the cursor at the start of the query
    LOOP    
        fetch demo_cursor into _r; 
        exit when not found;
        RETURN NEXT _r; 
    END LOOP;
    --
    -- close the cursor 
    CLOSE demo_cursor ; 
    --
END ; 
$fun1$ ; 

-- TEST : 
SELECT *
FROM public.demo_stashing__cursor( )
    AS f (topic_id bigint, problem_id bigint , image_id bigint, wkt text) ;
--> returns 2 rows.