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 | 41 | 2 | 3 | 41 | 2 | 3 | 41 | 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
