Re: Return Query with simple function - Mailing list pgsql-general

On 2/23/08, dvanatta <dvanatta@yahoo.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)