RETURN QUERY in PL/PgSQL?

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

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/

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

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]http://archives.postgresql.org/pgsql-patches/2007-04/msg00311.php; 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.