Re: Functions! - Mailing list pgsql-general

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