Re: Return Query with simple function

On 2/23/08, dvanatta <dvanatta(at)yahoo(dot)com> wrote:
> How should this function be written?

Define output parameters in the function and return SETOF RECORD, e.g.:

--- SQL ---
CREATE OR REPLACE FUNCTION
GET_FOO1(BAR INT, OUT BAZ INT, OUT ZAB INT)
RETURNS SETOF RECORD AS
$BODY$
SELECT $1, $1;
$BODY$
LANGUAGE 'SQL';

SELECT * FROM GET_FOO1(42);

baz | zab
-----+-----
42 | 42
(1 row)

--- PL/PgSQL ---
CREATE OR REPLACE FUNCTION
GET_FOO2(BAR INT, OUT BAZ INT, OUT ZAB INT)
RETURNS SETOF RECORD AS
$BODY$
BEGIN
RETURN QUERY SELECT BAR, BAR;
END;
$BODY$
LANGUAGE 'PLPGSQL';

SELECT * FROM GET_FOO2(42);

baz | zab
-----+-----
42 | 42
(1 row)