pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PLPGSQL 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>
  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-25 14:55  Roberto Mello <rmello@cc.usu.edu>
  parent: Otis Gospodnetic <otisg@ivillage.com>
  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-25 19:09  Jan Wieck <janwieck@yahoo.com>
  parent: Otis Gospodnetic <otisg@ivillage.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