PHP and PostgreSQL functions

[dkjohnson]

I'm using PostgreSQL and PHP to build a site with several dynamic reports. I'd like to define functions (i.e. stored procedures) in PostgreSQL that join multiple tables and return a number of columns in each row, based on parameter from the PHP code.

I can't seem to find much on returning rowsets from functions PostgreSQL. It seems that SQL functions won't work for other than single tables, where all columns are returned. For example,
this works in PgSQL:

CREATE FUNCTION "chemdesc" (INTEGER) RETURNS SETOF chems AS'
SELECT * FROM chems;
'LANGUAGE 'sql';

This doesn't:

CREATE FUNCTION "chemdesc" (INTEGER) RETURNS SETOF chems AS'
SELECT chem_id, chem_name, chem_rtecs FROM chems;
'LANGUAGE 'sql';

It looks like I'll have to do in pl/pgsql, if it's doable at all. Could anyone save me some time, and provide a very simple snippet of pl/pgsql to create a function that returns mulitple rows from a join query, or, just tell me I can't do that?

Thanks!😉

[chriskl]

You need to be using PostgreSQL 7.3 for full support for functions that return sets.

You can create a composite anonymous type in 7.3 eg:

CREATE TYPE blah AS (int4, int4, varchar);

(not real SQL)

And then use that as the return type of your function.

Chris