Lists: | pgsql-hackers |
---|
From: | Neil Conway <neilc(at)samurai(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RETURN QUERY in PL/PgSQL? |
Date: | 2007-04-23 21:17:00 |
Message-ID: | 1177363020.16415.156.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
In a PL/PgSQL set-returning function, returning the result set of a
query requires a FOR loop and repeated invocations of the RETURN NEXT
statement:
FOR x in SELECT ... LOOP
RETURN NEXT x;
END LOOP;
This works, but it seems overly verbose. It occurred to me that we could
easily add a new PL/PgSQL statement that evaluates a set-returning
expression and adds *all* the resulting rows to the function's result
set. For example:
RETURN QUERY SELECT ...;
I'm not sure of the right name: "RETURN ROWS" or "RETURN ALL" might also
work. Of course, this is syntax sugar (and superficial sugar at that),
but I believe this is a fairly common requirement.
Comments?
-Neil
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Cc: | Neil Conway <neilc(at)samurai(dot)com> |
Subject: | Re: RETURN QUERY in PL/PgSQL? |
Date: | 2007-04-23 21:23:29 |
Message-ID: | 200704231423.29895.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Neil,
> This works, but it seems overly verbose. It occurred to me that we could
> easily add a new PL/PgSQL statement that evaluates a set-returning
> expression and adds *all* the resulting rows to the function's result
> set. For example:
>
> RETURN QUERY SELECT ...;
>
> I'm not sure of the right name: "RETURN ROWS" or "RETURN ALL" might also
> work. Of course, this is syntax sugar (and superficial sugar at that),
> but I believe this is a fairly common requirement.
>
> Comments?
Hmmm ... "ALL" is already a reserved word, so is unlikely to be a variable
name, yes? I'd think we could get some breakage on "ROWS". So I'd go for
"RETURN ALL".
Overall, I think this is worthwhile, but maybe not enough to bypass feature
freeze.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Neil Conway <neilc(at)samurai(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: RETURN QUERY in PL/PgSQL? |
Date: | 2007-04-23 21:48:57 |
Message-ID: | 13967.1177364937@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Neil Conway <neilc(at)samurai(dot)com> writes:
> This works, but it seems overly verbose. It occurred to me that we could
> easily add a new PL/PgSQL statement that evaluates a set-returning
> expression and adds *all* the resulting rows to the function's result
> set. For example:
I think we've got something isomorphic to that in the patch queue
already --- take a look at Pavel's "table function" patch. It's in
need of cleanup but I think it will make it in.
regards, tom lane
From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Neil Conway <neilc(at)samurai(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: RETURN QUERY in PL/PgSQL? |
Date: | 2007-04-23 22:05:19 |
Message-ID: | 462D2D9F.2020603@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Tom Lane wrote:
> Neil Conway <neilc(at)samurai(dot)com> writes:
>
>> This works, but it seems overly verbose. It occurred to me that we could
>> easily add a new PL/PgSQL statement that evaluates a set-returning
>> expression and adds *all* the resulting rows to the function's result
>> set. For example:
>>
>
> I think we've got something isomorphic to that in the patch queue
> already --- take a look at Pavel's "table function" patch. It's in
> need of cleanup but I think it will make it in.
>
>
>
Interesting - I haven't followed that one. In pl/perl the equivalent
will use one tuplestore on the way in and another on the way out, with
return_next() copying between the two. If we had some mechanism like
this there is thus a potential for substantial savings, as well as
improved clarity.
cheers
andrew
From: | Neil Conway <neilc(at)samurai(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: RETURN QUERY in PL/PgSQL? |
Date: | 2007-04-24 06:00:00 |
Message-ID: | 1177394400.16415.186.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Mon, 2007-04-23 at 17:48 -0400, Tom Lane wrote:
> I think we've got something isomorphic to that in the patch queue
> already --- take a look at Pavel's "table function" patch. It's in
> need of cleanup but I think it will make it in.
Interesting -- I missed that patch, but it seems like a better approach.
Are you already reviewing Pavel's patch, or is it something I could take
a look at?
-Neil
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Neil Conway <neilc(at)samurai(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: RETURN QUERY in PL/PgSQL? |
Date: | 2007-04-24 06:10:12 |
Message-ID: | 24312.1177395012@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Neil Conway <neilc(at)samurai(dot)com> writes:
> Interesting -- I missed that patch, but it seems like a better approach.
> Are you already reviewing Pavel's patch, or is it something I could take
> a look at?
The main objection I have is that I don't think changing the definition
of pg_proc.proargmodes is a good idea --- that will break some
nontrivial amount of client-side code in order to support a distinction
that seems unimportant. IMHO anyway. Feel free to take a whack at it.
regards, tom lane