Hi All , 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 whether im right at the points A and B as I've marked in the code above . As I'm getting a load of unexplained errors. It would be great if someone could help me out with it . Thanks! :) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Stored-function-debugging-help-tp5028300p5028300.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
