如何从postgres函数返回临时表? [英] how to return temp table from postgres function?

问题描述

我在下面的查询中独立运行良好，但是在postgres函数中显示了许多问题

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;

我只需要一个类似

CREATE OR REPLACE FUNCTION getresourceinfo(opened_path int,tablename varchar) returns TABLE (pathid int,name varchar,pbs varchar, parentid varchar, resid int) AS $BODY$ 

只需要分别使用两个变量open_path和tablename分别用于880和tblinfo.我知道有很多关于返回表的帖子，但是我尝试了许多关于Postgres的基本知识后，是否有任何建议会很有帮助.如果您觉得我的查询很笨拙，请为我提供一个函数，该函数需要2个参数，分别是数字n和表名.假设有10列，其中之一是序列号，现在函数应该返回所有> n的行，而不是返回表名的2或3列的所有行.

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.

推荐答案

临时表

在标题中回答您的问题:
一个不能从postgres函数返回一个临时表". 临时表是创建的 ，并且在同一会话中对同一用户自动可见.在会话结束时(或更早)，它们会自动删除.

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).

但是可以像表一样使用返回集合的函数(也称为表函数"):

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

CREATE OR REPLACE FUNCTION getresourceinfo(tablename regclass, opened_path int)
  RETURNS TABLE (pathid int, name varchar, pbs varchar
               , parentid varchar, resid int) AS
$func$ 
BEGIN

RETURN 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')

为什么表参数的数据类型为regclass?
表名作为PostgreSQL函数参数

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.

这篇关于如何从postgres函数返回临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！