Stored function with temporary table in postgresql

Question

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;

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

`create function GetEmployees() returns setof employee as 'select * from employee;' language 'sql';` from this wiki: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions — triclosan, Nov 28 '11 at 15:53

score 6 · Accepted Answer · edited May 23 '17 at 11:50

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.

The way to go is to use RETURN NEXT or RETURN QUERY

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

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.

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

edit2:

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.

,Thanks!! that works perfectly well with a little modification, an unrelated question , is there a way to fetch the total number of records that would be returned from the above query? as clearly count(*) wont work. — seeker, Nov 29 '11 at 06:07

score 0 · Answer 2 · answered Nov 28 '11 at 13:22

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)

Stored function with temporary table in postgresql

2 Answers2

Linked