Thread: RETURN QUERY in PL/PgSQL?
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
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
Neil Conway <neilc@samurai.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
Tom Lane wrote: > Neil Conway <neilc@samurai.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
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
Hello It is RETURN TABLE(SQL) via ANSI SQL 2003 Table function support is in patch queue: http://archives.postgresql.org/pgsql-patches/2007-02/msg00216.php http://momjian.us/mhonarc/patches/msg00001.html Regards Pavel Stehule >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. _________________________________________________________________ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/
Neil Conway <neilc@samurai.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
On Tue, 2007-04-24 at 07:58 +0200, Pavel Stehule wrote: > It is RETURN TABLE(SQL) via ANSI SQL 2003 I think there are two basically orthogonal features in the patch: the "RETURNS TABLE" addition to CREATE FUNCTION, and the "RETURN TABLE" statement in PL/PgSQL. The former is specified by the SQL standard and is applicable to all PLs, while the latter is syntax sugar for PL/PgSQL. I think it would make sense to split the patch into two separate patches, one for each feature. I'm inclined to agree with Tom that adding PROARGMODE_TABLE isn't worth the trouble: making RETURNS TABLE(...) equivalent to RETURNS SETOF RECORD with OUT parameters strikes me as more elegant. I didn't really understand the "name collision" argument you made earlier[1]; can you elaborate? Another question is how RETURN NEXT and RETURN TABLE should interact (in PL/PgSQL). I think the two sensible choices are to either disallow a function from using both statements (which is what the patch currently does), or allow both statements to be used, and have RETURN TABLE *not* return from the function -- both RETURN TABLE and RETURN NEXT would append results to the function's result tuplestore. The latter seems more flexible. Do we need the extra set of parentheses in RETURN TABLE? To use one of your earlier examples: CREATE FUNCTION fooff(a int) RETURNS TABLE(a int, b int) AS $$ BEGIN RETURN TABLE(SELECT * FROM Foo WHERE x< a); END; $$ LANGUAGE plpgsql; "RETURN TABLE SELECT ... ;" should be sufficient to allow correct parsing, and is more consistent with the lack of parentheses in the other RETURN variants. -Neil [1] http://archives.postgresql.org/pgsql-patches/2007-04/msg00311.php
>I think there are two basically orthogonal features in the patch: the >"RETURNS TABLE" addition to CREATE FUNCTION, and the "RETURN TABLE" >statement in PL/PgSQL. The former is specified by the SQL standard and >is applicable to all PLs, while the latter is syntax sugar for PL/PgSQL. >I think it would make sense to split the patch into two separate >patches, one for each feature. it is good idea. > >I'm inclined to agree with Tom that adding PROARGMODE_TABLE isn't worth >the trouble: making RETURNS TABLE(...) equivalent to RETURNS SETOF >RECORD with OUT parameters strikes me as more elegant. I didn't really >understand the "name collision" argument you made earlier[1]; can you >elaborate? > for me RETURNS TABLE (a,b) isn't equialent for (OUT a, OUT b) RETURNS SETOF RECORD, but it's eq. for RETURNS SETOF RECORD ... and SELECT FROM foo() AS (a, b). Reason: example: I have table with attr. cust_id, and I want to use parametrized view (table function) where I want to have attr cust_id on output. Variant a) Tom proposal -- because _cust_id is variable CREATE OR REPLACE FUNCTION foo(arg int) RETURNS TABLE (_cust_id int) AS $$ BEGIN RETURN TABLE (SELECT cust_id FROM tab WHERE some = arg); END; $$ LANGUAGE plpgsql; SELECT * FROM foo(1) as (cust_id); Variant b) My proposal -- cust_id isn't variable CREATE OR REPLACE FUNCTION foo(arg int) RETURNS TABLE (cust_id int) AS $$ BEGIN RETURN TABLE (SELECT cust_id FROM tab WHERE some = arg); END; $$ LANGUAGE plpgsql; SELECT * FROM foo(1); Next argument. I would to use this for SQL/PSM. I didn't find any notice about equality between attributies from RETURNS TABLE clause and OUT variables. If you have TABLE function (RETURNS TABLE) you have to use table expression .. RETURN TABLE(SELECT ...) SQL/PSM doesn't know RETURN NEXT, and if I have accept your argument, then I will be in problems with some implicit variables. I need information, where attribute was used. How plpgsql use variable it is different question. If you want, use table attributes like out variables. plpgsql isn't standardised and then it isn't too important. SQL/PSM is defined, and there is important to difference between TABLE attributies and OUT variables. >Another question is how RETURN NEXT and RETURN TABLE should interact (in >PL/PgSQL). I think the two sensible choices are to either disallow a >function from using both statements (which is what the patch currently >does), or allow both statements to be used, and have RETURN TABLE *not* >return from the function -- both RETURN TABLE and RETURN NEXT would >append results to the function's result tuplestore. The latter seems >more flexible. RETURN TABLE is specified in std, and it's last statement. SQL/PSM knows it, and it can be source of problems for beginers in future. Maybe .. RETURN NEXT TABLE .... > >Do we need the extra set of parentheses in RETURN TABLE? To use one of >your earlier examples: > > CREATE FUNCTION fooff(a int) > RETURNS TABLE(a int, b int) AS $$ > BEGIN > RETURN TABLE(SELECT * FROM Foo WHERE x < a); > END; $$ LANGUAGE plpgsql; > >"RETURN TABLE SELECT ... ;" should be sufficient to allow correct >parsing, and is more consistent with the lack of parentheses in the >other RETURN variants. > again. std need it, but plpgsql isn't sql/psm language. And it is true, lack of parentheses is more consistent with other plpgsql constructs (not only RETURN statement). >-Neil > >[1] http://archives.postgresql.org/pgsql-patches/2007-04/msg00311.php > _________________________________________________________________ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/
Pavel, my apologies for not getting back to you sooner. On Wed, 2007-25-04 at 07:12 +0200, Pavel Stehule wrote: > example: I have table with attr. cust_id, and I want to use parametrized > view (table function) where I want to have attr cust_id on output. Hmm, I see your point. I'm personally satisfied with adding a new proargmode to solve this as you suggest. > RETURN TABLE is specified in std, and it's last statement. Where is RETURN TABLE defined in the standard? The only reference to TABLE I can see is as a <multiset value constructor> (section 6.39 in the current SQL 200n draft). That would allow RETURN TABLE(...), but it would also allow TABLE(...) to be used in other contexts. I think the right place to implement TABLE(...) per the spec would be in the backend, as part of an implementation of the standard's multiset concept. Therefore, we probably should *not* use RETURN TABLE in PL/PgSQL, since it would induce confusion if we ever do a proper multiset implementation. -Neil
Neil Conway <neilc@samurai.com> writes:
> Pavel, my apologies for not getting back to you sooner.
> On Wed, 2007-25-04 at 07:12 +0200, Pavel Stehule wrote:
>> example: I have table with attr. cust_id, and I want to use parametrized
>> view (table function) where I want to have attr cust_id on output.
> Hmm, I see your point. I'm personally satisfied with adding a new
> proargmode to solve this as you suggest.
This will break client-side code that looks at proargmode, and I don't
think the argument in favor is strong enough to justify that ...
regards, tom lane
2007/5/3, Tom Lane <tgl@sss.pgh.pa.us>: > Neil Conway <neilc@samurai.com> writes: > > Pavel, my apologies for not getting back to you sooner. > > On Wed, 2007-25-04 at 07:12 +0200, Pavel Stehule wrote: > >> example: I have table with attr. cust_id, and I want to use parametrized > >> view (table function) where I want to have attr cust_id on output. > > > Hmm, I see your point. I'm personally satisfied with adding a new > > proargmode to solve this as you suggest. > > This will break client-side code that looks at proargmode, and I don't > think the argument in favor is strong enough to justify that ... > can be. But similar changes was more times: named arguments, out, inout attrb .. This depend on application. If any application is written too simply then it can have problem. But which application check proargmodes: pgadmin, phppgadmin, emsmanager, ... it's not frequentation activity. And it's question for maintainers of this applications. What difficult is change it? This syntax is usefull. It lowers risk of name's colisition, and is more readable (if it do what it have to do). I am sorry, but I don't see sense of "new" table function syntax without changes of proargmodes. Only shortcut for SETOF RECORD isn't usefull. This syntax is standardised, is used in SQL/PSM which PostgreSQL have to adapt this year, or next year, or maybe later, but have to be adapted. And SQL/PSM knows only declared variables or function's parameters. I forgot, it's can be usefull for SQL language procedures. They don't use named arguments (how long?). Regards Pavel Stehule
On 4/25/07, Pavel Stehule <pavel.stehule@hotmail.com> wrote: > for me RETURNS TABLE (a,b) isn't equialent for (OUT a, OUT b) RETURNS SETOF > RECORD, but > it's eq. for RETURNS SETOF RECORD ... and SELECT FROM foo() AS (a, b). > > Reason: > > example: I have table with attr. cust_id, and I want to use parametrized > view (table function) where I want to have attr cust_id on output. You can use table aliases to avoid conflicts. (t.cust_id) -- marko
Tom, Pavel, > > Hmm, I see your point. I'm personally satisfied with adding a new > > proargmode to solve this as you suggest. > > This will break client-side code that looks at proargmode, and I don't > think the argument in favor is strong enough to justify that ... What kind of client-side code are we talking about breaking? Just pgAdmin & phpPgAdmin and the like, or potentially other stuff that calls stored procedures? FWIW, pgAdmin3 1.6 *already* breaks on 8.3 procedures, so we have client-side breakage already. -- Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus wrote: > Tom, Pavel, > >>> Hmm, I see your point. I'm personally satisfied with adding a new >>> proargmode to solve this as you suggest. >> This will break client-side code that looks at proargmode, and I don't >> think the argument in favor is strong enough to justify that ... > > What kind of client-side code are we talking about breaking? Just pgAdmin & > phpPgAdmin and the like, or potentially other stuff that calls stored > procedures? > > FWIW, pgAdmin3 1.6 *already* breaks on 8.3 procedures, so we have client-side > breakage already. > If we're going to make a change, please do so sooner rather than later. I'd like to take pgAdmin to beta Real Soon Now if possible. Regards, Dave.