How To Return Temp Table From Postgres Function?

I have below query running fine independently, but showing many issues inside a postgres function CREATE TEMP TABLE tbl (h ltree, pathid int) ; CREATE TEMP TABLE temp_res (pathid

Solution 1:

Temporary table

To answer your question in the title: One cannot "return a temp table from postgres function". Temporary tables are created and automatically visible to the same user within the same session. They are dropped automatically at the end of the session (or sooner).

Table function

But a set-returning function (a.k.a. "table function") can be used just like a table:

CREATEOR REPLACE FUNCTION getresourceinfo(tablename regclass, opened_path int)
  RETURNSTABLE (pathid int, name varchar, pbs varchar
               , parentid varchar, resid int) AS
$func$ 
BEGINRETURN QUERY EXECUTE format(
  'SELECT t.pathid, t.name, t.pbs, t.parentid, t.resid
   FROM  '|| tablename ||' t
   WHERE  t.opened_path = $1'
   )
USING opened_path;

END
$func$ LANGUAGE plpgsql;

Would only make sense for a bunch of tables that all share the hard coded column names with the same data type. Call (just like selecting from a table):

SELECT*FROM getresourceinfo(1, 'my_schema.my_tbl')

Why the data type regclass for the table parameter? Table name as a PostgreSQL function parameter

Cursor

For completeness: One can return a CURSOR, which would be a very similar concept as what you ask for. Details in the manual here. But I hardly ever use cursors. Table functions are more practical most of the time.