PostgreSQL中带有临时表的存储函数

Question

我对在PostgreSQL中编写存储函数以及一般编程都很陌生。我试图编写一个带有输入参数并返回存储在临时表中的结果集的函数。

我在函数中执行以下步骤：

1. 获取所有消费者的列表，并将其ID存储在临时表中。
2. 迭代遍历特定表格，并检索与上述列表中每个值对应的值，并将其存储在临时表中。
3. 返回临时表。

下面是我自己尝试编写的函数：

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的点。同时，在尝试创建临时表时出现了大量错误。

编辑：已使该函数可用，但当我尝试运行该函数时会出现以下错误。

   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;

- seeker

---

如果您喜欢，可以返回行集而不是临时表。 - triclosan

@triclosan，你能否提供一个例子？ - seeker

创建函数GetEmployees()，返回employee集合，代码为'select * from employee;'，语言为'sql'; 从此wiki链接：http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions - triclosan

Answer 1

据我所知，在postgres中无法将表声明为变量。您可以在函数体中创建一个表并在其中使用它（甚至在函数外部也可以）。但要注意，临时表直到会话结束或提交之前才会被删除。

解决方法是使用RETURN NEXT or RETURN QUERY。

至于函数结果类型，我发现RETURNS TABLE更易读。

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

在您的情况下：

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

编辑2：

你可能想看一下这个解决方案，因为那正是你在这里所处理的群组最大值问题。

- soulcheck

---

你看了我发布的编辑内容了吗？ - seeker

谢谢！稍作修改就可以完美运行了。还有一个无关的问题，是否有一种方法可以获取上述查询返回的记录总数？显然count(*)不起作用。 - seeker

Answer 2

可能更容易的方法是返回一个表格（或查询）

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;

（从postgresql文档复制）

- number5