在PostgreSQL中具有临时表的存储函数 [英] Stored function with temporary table in postgresql

问题描述

对于在Postgresql和一般情况下编写存储函数是不熟悉的。我正在尝试使用输入参数编写onw并返回存储在临时表中的一组结果。
我在函数中执行以下操作。
1）获取所有消费者的列表，并将其ID存储在临时表中。
2）遍历特定表并从上面的列表中检索与每个值相对应的值，并将其存储在临时表中。
3）返回临时表。

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.

这是我自己尝试编写的函数，

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=$1--Point A 
order by db_consumer_pump_details.consumer_id,pump_id,createddate desc limit 2 
END LOOP; 
return tmp_table   
END; 
$$ 
LANGUAGE plpgsql; 

但是我不确定我的方法以及我是否正确地掌握了A点和B点

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.

EDIT ：使该函数起作用，但是尝试运行该函数时出现以下错误。

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

这是我修改过的函数。

 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=$1
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;

推荐答案

AFAIK不能在postgres中将表声明为变量。您可以做的是在功能体内创建一个并在之后使用（甚至在功能之外）。不过要当心，因为临时表直到会话或提交结束才被删除。

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.

方法是使用返回下一个或返回查询

对于函数结果类型，我总是发现RETURNS TABLE更具可读性。

As for the function result type I always found RETURNS TABLE to be more readable.

编辑：
您的cons_id数组是不必要的，只需迭代select返回的值即可。
另外，您可以在单个函数中有多个return查询语句，以将查询结果附加到函数返回的结果中。

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.

在您的情况下：

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=$1
        ORDER BY db_consumer_pump_details.consumer_id,pump_id,createddate DESC LIMIT 2 
    );
    END LOOP;
END;
$$

edit2：

您可能想看看这是针对Groupwise-k-maximum问题的解决方案，因为这正是您在此处要处理的问题。

You probably want to take a look at this solution for groupwise-k-maximum problem as that's exactly what you're dealing with here.

这篇关于在PostgreSQL中具有临时表的存储函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！