Thread: Stored function debugging help
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.
On 11/28/11 1:30 AM, JavaNoobie wrote: > 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. couldn't that all be done by a JOIN without involving a temporary table, or iteration? this seems like a conventional programmers approach to problem solving, rather than using the power of the relational database. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Well I'm not fond of using a temporary table either. But how would I be able to iterate over a set of consumers while using a join ? From my (limited) , using only a join I would only be able to generate the data for a particular consumer , rather than all of them. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Stored-function-debugging-help-tp5028300p5028732.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 28 November 2011 13:36, JavaNoobie <vivek.mv@enzentech.com> wrote: > Well I'm not fond of using a temporary table either. But how would I be able > to iterate over a set of consumers while using a join ? From my (limited) , > using only a join I would only be able to generate the data for a particular > consumer , rather than all of them. It would seem that the join that you already use inside your for-loop would give you the results you want, precisely because of the join that's in it. Provided you take off the limit, of course. Perhaps you want those results DISTINCT ON (consumer_id), but a for-loop is definitely not the way to do that. Not impossible, just very inelegant and slow. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
On 11/28/11 4:36 AM, JavaNoobie wrote:
> Well I'm not fond of using a temporary table either. But how would I be able
> to iterate over a set of consumers while using a join ? From my (limited) ,
> using only a join I would only be able to generate the data for a particular
> consumer , rather than all of them.
get rid of
db_consumer_pump_details.consumer_id=i and
and the limit, and the join will do all of them. But, maybe I don't
quite understand what it is you're doing.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast