Re: Re[GENERAL] turn Query with simple function

[Rodrigo E. De León Plicet]

On 2/23/08, dvanatta <[EMAIL PROTECTED]> 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)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend