Postgres Pro
Downloads
Home   >   mailing lists

Thread: Returning multiple rows from a function?

Returning multiple rows from a function?

From
Bret Schuhmacher
Date:
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@thelastmilellc.com

Re: Returning multiple rows from a function?

From
brian
Date:

Bret Schuhmacher wrote:
> 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,
>

Use "RETURNS SETOF record" and "FOR r IN ... LOOP RETURN NEXT; END LOOP;
RETURN;"

brian

Re: Returning multiple rows from a function?

From
Volkan YAZICI
Date:
On Nov 27 11:59, Bret Schuhmacher wrote:
> I'm trying to return multiple rows from a function, but all I can get
> ...
>
> CREATE OR replace function getOnCallVol() RETURNS RECORD AS $$

You should return "SETOF record". See related section of the manual
about SRFs (Set Returning Fucntions).


Regards.