From: Roberto Mello <rmello@cc.usu.edu>
To: Otis Gospodnetic <otisg@ivillage.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: PLPGSQL func. defn. for returning resultset?
Date: Mon, 25 Feb 2002 07:55:12 -0700
Message-ID: <20020225145512.GB24707@cc.usu.edu> (raw)
In-Reply-To: <3C794AAE.3080405@ivillage.com>
References: <3C794AAE.3080405@ivillage.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rmello@cc.usu.edu, otisg@ivillage.com
Subject: Re: PLPGSQL func. defn. for returning resultset?
In-Reply-To: <20020225145512.GB24707@cc.usu.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox