Re: Returning RECORD from PGSQL without custom type?

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
$$ ...

$$

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

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
>