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:
>Hi All,
>
>Can a query be cast as function: i have a query say like:
>select * from tableName where signal>=2000 and signal <=200000 and flag>=0;
>
>How can one say in a function "generic tablename"!!
>
>Thanks,
>Hrishi
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>