From: | Bret Schuhmacher <bret(at)thelastmilellc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Returning multiple rows from a function? |
Date: | 2006-11-27 16:59:03 |
Message-ID: | 456B1957.1010906@thelastmilellc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I'm trying to return multiple rows from a function, but all I can get
with the code below is the first row. I got most of the function below
off the net and I think the problem is the first "RETURN" statement,
which stops the loop.
CREATE OR replace function getOnCallVol() RETURNS RECORD AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN
select fname,lname,phone1,phone2,phone3,phone4,phone5
from events e,volunteer v
where (now() >= starttime and now()<=endtime and e.v_id =
v.v_id)
OR (fname='Backup') limit 2
LOOP
return r;
END LOOP;
RETURN null;
END;
$$ Language plpgsql;
When I run the SQL alone, I get two rows, as I should:
Mary Smith 1111111111 2222222222 3333333333
Backup Cellphone 3319993
However, if I run it via the function (i.e. select getOnCallVol()), I
get this:
(Mary,Smith,1111111111,2222222222,3333333333,"","")
Is there another way to get each row returned? I played around with
making the function return a "SETOF RECORD" and using "RETURN NEXT", but
had no luck.
Thanks,
Bret
--
Bret Schuhmacher
bret(at)thelastmilellc(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2006-11-27 17:00:31 | Re: -b command line parameter in pg_dump |
Previous Message | Andrus | 2006-11-27 16:36:36 | -b command line parameter in pg_dump |