PostgreSQL User Defined table type in memory

Question

If got several stored procedures (functions in PG) that need to pass around an intermediate result set between them (1K rows at most)

I can do this in other DBMS's but would like to know how to accomplish this in native PostgreSQL.

Specifically does PG support:

CREATE TYPE MyTable AS TABLE(...)

Also can we do this completely in memory?

I want to pass a "MyTable" between several procs as input and output.

Or possibly could I build this result set and pass it around as a JSONB parameter?

I need this to be blazingly fast, no matter how it's done.

Answer 1

The question is a bit unclear, but the best way is probably to pass a cursor between the functions.

Here is a toy example, see the documenation for more:

CREATE FUNCTION a() RETURNS refcursor
   LANGUAGE plpgsql AS
$$DECLARE
   c refcursor := 'cursorname';
BEGIN
   OPEN c FOR SELECT 42 AS col1;
   RETURN c;
END;$$;

CREATE FUNCTION b() RETURNS SETOF integer
   LANGUAGE plpgsql AS
$$DECLARE
   c refcursor;
   r record;
BEGIN
   c := a();

   LOOP
      FETCH NEXT FROM c INTO r;
      EXIT WHEN NOT FOUND;
      RETURN NEXT r.col1;
   END LOOP;
END;$$;

That example works like this:

SELECT * FROM b();

 b  
----
 42
(1 row)

Answer 2

You could pass an array of the table's type around:

create function function_one()
  returns void
$$
declare
  l_rows my_table[];
begin
  select array_agg(mt)
    into l_rows
  from my_table mt
  where ...;

  perform function_two(l_rows);
  perform function_three(l_rows);
end;
$$
language plpgsql;