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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
