Re: RETURN QUERY in PL/PgSQL? - Mailing list pgsql-hackers

>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/