Thread: PLPGSQL func. defn. for returning resultset?
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 ????? I could not find any examples for such cases. Thanks, Otis
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.
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 ????? > > I could not find any examples for such cases. And it would really surprise me if you could find examples of that. The only way you currently can return a result set would be through a cursor, opened in your function. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com