From: | Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com> |
---|---|
To: | oksanay(at)athabascau(dot)ca, pgsql-general(at)postgresql(dot)org |
Subject: | Re: plpgsql: return multiple result sets |
Date: | 2003-10-18 16:57:53 |
Message-ID: | 20031018165753.70882.qmail@web20806.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
--- Oksana Yasynska <oksana(at)athabascau(dot)ca> wrote:
> Hi all,
>
> I'm running Postgres 7.2.1 and I need to return
> multiple row sets from plpgsql
> function. I'm new in plpgsql but according
> documentation and everything I
> could find in the mailing list I need to switch to
> 7.3 to get at least SETOF
> rows as a result.
>
> I can't really upgrade Postgres now. Is there is any
> a workaround idea to
> retrieve multiple rowsets?
>
> I have up to 50 tables in database to join and pass
> this data to the another
> application
> I had an idea to build a view and retrieve cursor on
> this view (if I stay with
> 7.2) or generate custom type based on the columns of
> all 50 tables and
> retrieve a SETOF custom type (if I use 7.3)
>
> Can anybody give me any suggestion?
You can return a cursor from your function, which you
can then use in your application. Sort of like:
create function my_cursor_test(refcursor, integer)
returns refcursor as 'begin open $1 as cursor for
select * from mytable where id = $2; return $1; end;'
language 'plpgsql';
Then call it like:
begin;
select my_cursor_test(mycursor, 1);
select * from mycursor;
(output comes here)
end;
Note the need to wrap the statements in an explicit
transaction. With statements being autocommitted, the
cursor would be closed immediately following the
function call.
Better check the syntax too, I just dashed that off
(hey, it's Saturday). It's all there in the 7.2 docs
under "procedural languages".
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-10-18 17:12:41 | Re: 7.4b4 domain usage and select question |
Previous Message | Jitender Kumar C | 2003-10-18 16:51:03 | Timestamp to date conversion...plz help me |