postgresql 中带有临时表的存储函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8296034/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored function with temporary table in postgresql
提问by seeker
Im new to writing stored functions in postgresql and in general . I'm trying to write onw with an input parameter and return a set of results stored in a temporary table. I do the following in my function . 1) Get a list of all the consumers and store their id's stored in a temp table. 2) Iterate over a particular table and retrieve values corresponding to each value from the above list and store in a temp table. 3)Return the temp table.
我是在 postgresql 和一般情况下编写存储函数的新手。我正在尝试使用输入参数写入 onw 并返回存储在临时表中的一组结果。我在我的函数中执行以下操作。1) 获取所有消费者的列表并将他们的 id 存储在临时表中。2) 迭代特定表并从上述列表中检索与每个值对应的值并存储在临时表中。3）返回临时表。
Here's the function that I've tried to write by myself ,
这是我自己尝试编写的函数，
create or replace function getPumps(status varchar) returns setof record as $$ (setof record?)
DECLARE
cons_id integer[];
i integer;
temp table tmp_table;--Point B
BEGIN
select consumer_id into cons_id from db_consumer_pump_details;
FOR i in select * from cons_id LOOP
select objectid,pump_id,pump_serial_id,repdate,pumpmake,db_consumer_pump_details.status,db_consumer.consumer_name,db_consumer.wenexa_id,db_consumer.rr_no into tmp_table from db_consumer_pump_details inner join db_consumer on db_consumer.consumer_id=db_consumer_pump_details.consumer_id
where db_consumer_pump_details.consumer_id=i and db_consumer_pump_details.status=--Point A
order by db_consumer_pump_details.consumer_id,pump_id,createddate desc limit 2
END LOOP;
return tmp_table
END;
$$
LANGUAGE plpgsql;
However Im not sure about my approach and whether im right at the points A and B as I've marked in the code above.And getting a load of errors while trying to create the temporary table.
但是，我不确定我的方法，以及我是否在上面的代码中标记的 A 点和 B 点正确。并且在尝试创建临时表时出现大量错误。
EDIT: got the function to work ,but I get the following error when I try to run the function.
编辑：使该函数正常工作，但是当我尝试运行该函数时出现以下错误。
ERROR: array value must start with "{" or dimension information
Here's my revised function.
这是我修改后的函数。
create temp table tmp_table(objectid integer,pump_id integer,pump_serial_id varchar(50),repdate timestamp with time zone,pumpmake varchar(50),status varchar(2),consumer_name varchar(50),wenexa_id varchar(50),rr_no varchar(25));
select consumer_id into cons_id from db_consumer_pump_details;
FOR i in select * from cons_id LOOP
insert into tmp_table
select objectid,pump_id,pump_serial_id,repdate,pumpmake,db_consumer_pump_details.status,db_consumer.consumer_name,db_consumer.wenexa_id,db_consumer.rr_no from db_consumer_pump_details inner join db_consumer on db_consumer.consumer_id=db_consumer_pump_details.consumer_id where db_consumer_pump_details.consumer_id=i and db_consumer_pump_details.status=
order by db_consumer_pump_details.consumer_id,pump_id,createddate desc limit 2;
END LOOP;
return query (select * from tmp_table);
drop table tmp_table;
END;
$$
LANGUAGE plpgsql;
回答by soulcheck
AFAIK one can't declare tables as variables in postgres. What you can do is create one in your funcion body and use it thourough (or even outside of function). Beware though as temporary tables aren't dropped until the end of the session or commit.
AFAIK 不能在 postgres 中将表声明为变量。您可以做的是在您的功能体中创建一个并在整个过程中（甚至在功能之外）使用它。但要注意临时表在会话结束或提交之前不会被删除。
The way to go is to use RETURN NEXT or RETURN QUERY
要走的路是使用RETURN NEXT 或 RETURN QUERY
As for the function result type I always found RETURNS TABLE to be more readable.
至于函数结果类型，我总是发现 RETURNS TABLE 更具可读性。
edit:Your cons_id array is innecessary, just iterate the values returned by select. Also you can have multiple return query statements in a single function to append result of the query to the result returned by function.
编辑：您的 cons_id 数组是不必要的，只需迭代 select 返回的值。您也可以在单个函数中使用多个返回查询语句，以将查询结果附加到函数返回的结果中。
In your case:
在你的情况下：
CREATE OR REPLACE FUNCTION getPumps(status varchar)
RETURNS TABLE (objectid INTEGER,pump_id INTEGER,pump_serial_id INTEGER....)
AS
$$
BEGIN
FOR i in SELECT consumer_id FROM db_consumer_pump_details LOOP
RETURN QUERY(
SELECT objectid,pump_id,pump_serial_id,repdate,pumpmake,db_consumer_pump_details.status,db_consumer.consumer_name,db_consumer.wenexa_id,db_consumer.rr_no FROM db_consumer_pump_details INNER JOIN db_consumer ON db_consumer.consumer_id=db_consumer_pump_details.consumer_id
WHERE db_consumer_pump_details.consumer_id=i AND db_consumer_pump_details.status=
ORDER BY db_consumer_pump_details.consumer_id,pump_id,createddate DESC LIMIT 2
);
END LOOP;
END;
$$
edit2:
编辑2：
You probably want to take a look at thissolution for groupwise-k-maximum problem as that's exactly what you're dealing with here.
您可能想看看groupwise-k-maximum 问题的这个解决方案，因为这正是您在这里处理的问题。
回答by number5
it might be easier to just return a table (or query)
只返回表（或查询）可能更容易
CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
RETURN QUERY SELECT quantity, quantity * price FROM sales
WHERE itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;
(copied from postgresql docs)
（复制自postgresql 文档）
