Re: PLPGSQL func. defn. for returning resultset?

On Sun, Feb 24, 2002 at 03:18:54PM -0500, Otis Gospodnetic wrote:
> Hello,
>
> I need to write some functions/stored procedures in PLPGSQL.
> All examples have a return clause that returns a single item (e.g.
> RETURN INTEGER or RETURN VARCHAR, etc.).
>
> However, I need to return a result set (e.g. SELECT user_id, name FROM
> users). What do I specify in the return part of the FUNCTION definition
> for that?
> CREATE FUNCTION foo() RETURNS ????? AS ' .....
> What do I put in place of ?????

If you're creating a SQL function it should be "setof" as described in the
reference for the "CREATE FUNCTION" command. All other types of function
use "refcursor", as described in the 7.2 and beyond documentation.

"setof" works only for SQL functions and works in 7.1 (and I think 7.0 as well).
"refcursor" only works for 7.2 and above.

Let me paste what I replied to a question like this a couple days ago (BTW,
it's a good idea to search the list archives at http://fts.postgresql.org/)

See this:
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html

Especially the section "Using Cursors". This is the 7.3devel
documentation, but this section should work just as well for 7.2 (this
WON'T work for 7.1 and below).

-Roberto

--
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
My last original thought died of loneliness.