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)