Re: Functions!

Tony Caduto <tony_caduto@xxxxxxxxxxxxxxxxxxxx> · Sat, 07 May 2005 11:25:34 -0500

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@xxxxxxxxxxxxxx

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend