pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPLPGSQL func. defn. for returning resultset?
3+ messages / 3 participants
[nested] [flat]
* PLPGSQL func. defn. for returning resultset?
@ 2002-02-24 20:18 Otis Gospodnetic <otisg@ivillage.com>
2002-02-25 14:55 ` Re: PLPGSQL func. defn. for returning resultset? Roberto Mello <rmello@cc.usu.edu>
2002-02-25 19:09 ` Re: PLPGSQL func. defn. for returning resultset? Jan Wieck <janwieck@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: Otis Gospodnetic @ 2002-02-24 20:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PLPGSQL func. defn. for returning resultset?
2002-02-24 20:18 PLPGSQL func. defn. for returning resultset? Otis Gospodnetic <otisg@ivillage.com>
@ 2002-02-25 14:55 ` Roberto Mello <rmello@cc.usu.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Roberto Mello @ 2002-02-25 14:55 UTC (permalink / raw)
To: Otis Gospodnetic <otisg@ivillage.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PLPGSQL func. defn. for returning resultset?
2002-02-24 20:18 PLPGSQL func. defn. for returning resultset? Otis Gospodnetic <otisg@ivillage.com>
@ 2002-02-25 19:09 ` Jan Wieck <janwieck@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jan Wieck @ 2002-02-25 19:09 UTC (permalink / raw)
To: Otis Gospodnetic <otisg@ivillage.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-02-25 19:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-24 20:18 PLPGSQL func. defn. for returning resultset? Otis Gospodnetic <otisg@ivillage.com>
2002-02-25 14:55 ` Roberto Mello <rmello@cc.usu.edu>
2002-02-25 19:09 ` Jan Wieck <janwieck@yahoo.com>
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