Functions!

To do it the way you have there you have to use a set returning function.
See the docs on set returning functions, you also must create a type
that defines the output types.
examples can be found here:
http://www.postgresql.org/docs/8.0/interactive/sql-createtype.html
http://www.postgresql.org/docs/8.0/interactive/sql-select.html

This example shows how to use a function in the FROM clause, both with
and without a column definition list:

CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors(111);
did | name
-----+-------------
111 | Walt Disney

A easier way would be to use a function that returns a refcursor, then
you would access it like this:

select myfunction();
fetch all from return_cursor;

return_cursor is the name of the refcursor.

http://www.postgresql.org/docs/8.0/interactive/plpgsql-cursors.html

Hope this helps,

Tony Caduto
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql 8.x

Hrishikesh Deshmukh wrote: