Re: Returning RECORD from PGSQL without custom type?

Hello

there exist table returning functions patch, but newer been applied.
It's some what you need.

Why don't you use OUT variables?

try

CREATE OR REPLACE FUNCTION fce(IN value int, OUT a int, OUT b int)
RETURNS SETOF record AS $$
BEGIN
  FOR i IN 1..$1 LOOP
    a := i + 1; b := i + 2;
    RETURN NEXT;
  END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE:

SELECT * FROM fce(10);

http://www.postgresql.org/docs/8.3/interactive/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

Regards
Pavel Stehule

2008/5/10 D. Dante Lorenso <dante@lorenso.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;
>
> 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;
>
> 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
>