Re: dynamic OUT parameters?

> Gerardo Herzig wrote:
>
>> 1) There is a way to make a function returning "any amount of any type
>> of arguments"?
>
> RETURNS SETOF RECORD
>
> The application must, however, know what columns will be output by the
> function ahead of time and call it using an explicit column declaration
> list. For example, the following function returns a table of width
> `_ncols' columns repeated over `_ncols' records:
>
> CREATE OR REPLACE FUNCTION dyncol(_ncols INTEGER) RETURNS SETOF RECORD
> AS
> $$
> DECLARE
> _out RECORD;
> _stm text;
> _i integer;
> BEGIN
> _stm = 'SELECT 1';
> FOR _i IN 2.._ncols LOOP
> _stm = _stm || ', ' || _i;
> END LOOP;
> _stm = _stm || ' FROM generate_series(1,' || _ncols || ');' ;
> FOR _out IN EXECUTE _stm LOOP
> RETURN NEXT _out;
> END LOOP;
> END;
> $$ LANGUAGE 'plpgsql';
>
>
> Because Pg must know what the return columns will be before the function
> is called, you can't just call it as `dyncol(4)' :
>
> test=> SELECT dyncol(4);
> ERROR: set-valued function called in context that cannot accept a set
> CONTEXT: PL/pgSQL function "dyncol" line 12 at RETURN NEXT
>
> you must instead specify a table alias with a column definition, eg:
>
> test=> SELECT * FROM dyncol(4) AS x(a INTEGER, b INTEGER, c INTEGER, d
> INTEGER);
> a | b | c | d
> ---+---+---+---
> 1 | 2 | 3 | 4
> 1 | 2 | 3 | 4
> 1 | 2 | 3 | 4
> 1 | 2 | 3 | 4
> (4 rows)
>
>
> Of course, nothing stops you from writing another function that provides
> this information to the application, so it can call the first function
> to get the information required to correctly call your dynamic reporting
> function.
>
>> 2) Can i make a special type "on_the_fly" and returning setof
>> "that_type"?
>
> You're better off using SETOF RECORD, at least in my opinion.
>
> --
> Craig Ringer
Oh, that looks promising. I wrongly supposed that RETURNING SETOF RECORD
forces the use of OUT parameters. I will give your idea a try.

Thanks Craig!
Gerardo