Thread: Return Query with simple function

I'm new to PostgreSQL, moving over from SQL Server and stored procs to
PostgreSQL 8.3 and it's functions and having some difficulty.  With SQL
Server I didn't have to define types and return those types from stored
procs which was convenient.  I came across Return Query but can't find much
info on how to use it.  I'm hoping Return Query will allow me to NOT have to
define types. I want to do the following but it doesn't work and I don't get
very helpful error info.  How should this function be written?  Do I have to
define a type and fill it?

CREATE OR REPLACE FUNCTION get_person(PersonId integer)
$BODY$
  BEGIN
    Return Query
    SELECT
    p.fname, j.title
    FROM   person p
         Inner join job j
        on p.personid = j.personid
    WHERE p.personid = PersonID;
  END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
--
View this message in context: http://www.nabble.com/Return-Query-with-simple-function-tp15659266p15659266.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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)