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.