Обсуждение: Stored function debugging help

Поиск
Список
Период
Сортировка

Stored function debugging help

От
JavaNoobie
Дата:
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.

Re: Stored function debugging help

От
John R Pierce
Дата:
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


Re: Stored function debugging help

От
JavaNoobie
Дата:
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.

Re: Stored function debugging help

От
Alban Hertroys
Дата:
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.

Re: Stored function debugging help

От
John R Pierce
Дата:
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