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 [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
