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 int, res_count int) ;
insert into tbl select l_tree,pathid from tblinfo where parentid in (880);
insert into temp_res select T.pathid pathid from tblinfo p1, tbl T where index(p1.l_tree,T.h ) != -1 GROUP BY T.pathid order by T.pathid;
select p.pathid pathid, p.name name, p.PBS PBS,p.parentid parentid,p.resid resid from tblinfo p, temp_res t where t.pathid = p.pathid;
i just need a function like
CREATE OR REPLACE FUNCTION getresourceinfo(opened_path int,tablename varchar) returns TABLE (pathid int,name varchar,pbs varchar, parentid varchar, resid int) AS $BODY$
just need to use two variables opened_path and tablename for 880 and tblinfo respectively. I know there many posts about returning tables but I am asking after trying many of them to my basic postgres knowledge any suggestions would be of great help. If you feel my query is clumsy please just help me with one function that takes 2 arguments a number n and tablename. Assume there are 10 columns and one of them is serial number now function should return all rows >n and not all but 2 or 3 columns of tablename.
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:
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):
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.