Re: Returning RECORD from PGSQL without custom type?

Pavel Stehule wrote:
> Hello
> 2008/5/10 D. Dante Lorenso <dante@larkspark.com>:
>> Instead of doing this:
>>
>>  CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
>>  RETURNS SETOF record AS
>>  $body$
>>  ...
>>  $body$
>>  LANGUAGE 'plpgsql' VOLATILE;
>>
>> I'd like to be able to do this:
>>
>>  CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
>>  RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS
>>  $body$
>>  ...
>>  $body$
>>  LANGUAGE 'plpgsql' VOLATILE;
>>
>
> Standard syntax via ANSI SQL is
> CREATE FUNCTION foo(params)
> RETURNS TABLE(fields of output table) AS
> $$ ...
> $$

Ah, this sound almost exactly like what I'm wanting!  So ... you are
saying that developers are working on something like?  I'm running 8.3
... would I find this feature in 8.4 or is it still not included in any
release?

>> Because this is the only function that will be returning that TYPE and I
>> don't want to have to create a separate type definition just for the return
>> results of this function.
>>
>> Maybe even more cool would be if the OUT record was already defined so that
>> I could simply select into that record to send our new rows:
>>   RETURN NEXT OUT;
>>   OUT.col1name := 12345;
>>   RETURN NEXT OUT;
>>   SELECT 12345, 'sample'
>>   INTO OUT.col1name, OUT.col2name;
>>   RETURN NEXT OUT;
>
> it's good idea - it was probably main problem of last patch in
> plpgsql. In this syntax is clear what is output, so RETURN NEXT
> statement can be without params. I am only not sure about name of
> default variable - maybe result is better.

Yeah, RESULT works too.  I'm not particular about what it has to be ...
just that something like that might exist.

Where can I go to follow development of this or test it out?  I see some
old threads now that I know what to look for:

http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php
http://archives.postgresql.org/pgsql-patches/2007-02/msg00216.php
http://archives.postgresql.org/pgsql-patches/2007-02/msg00341.php
http://archives.postgresql.org/pgsql-hackers/2007-09/msg01079.php

I want to make sure this patch/proposal covers my needs and
expectations.  Specifically I want to return records that are not simple
a straight query:

   CREATE OR REPLACE FUNCTION foo(f integer)
   RETURNS TABLE(a int, b int) AS
   $$
   DECLARE
     my_a INT;
     my_b INT;
   BEGIN
     -- 1) perhaps like this
     SELECT 1, 2
     INTO RESULT.a, RESULT.b;
     RETURN NEXT RESULT;

     -- 2) maybe like this
     RETURN NEXT 3, 4;  -- a=3, b=4

     -- 3) how about like this
     my_a := 5;
     my_b := 6;
     RETURN NEXT my_a, my_b;

     -- 4) maybe like this
     RETURN NEXT QUERY SELECT a, b FROM sometable x WHERE x.f = f;

     -- done
     RETURN;
   END;
   $$ LANGUAGE plpgsql;

Usage:

   SELECT a, b
   FROM foo(20);

Results:

   a  |  b
   ---+----
    1 |  2   <-- 1)
    3 |  4   <-- 2)
    5 |  6   <-- 3)
   ...       <-- 4) results from sometable WHERE x.f = 20

What do you think, will I be able to do all of this?

-- Dante

> Regards
> Pavel Stehule
>
>> Just as you've allowed me to define the IN variable names without needing
>> the legacy 'ALIAS $1 ...' format, I'd like to name the returned record
>> column names and types in a simple declaration like I show above.
>>
>> Does this feature request make sense to everyone?  It would make programming
>> set returning record functions a lot easier.
>>
>> -- Dante
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>