pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSETOF
8+ messages / 4 participants
[nested] [flat]
* SETOF
@ 2000-11-29 23:35 Limin Shen <limin_shen@hotmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Limin Shen @ 2000-11-29 23:35 UTC (permalink / raw)
To: pgsql-sql
<html><DIV>Hi,</DIV>
<DIV>This is my first time using this address, so if I sent to the wroing place, please forgive me.</DIV>
<DIV>I have a question on how to use SETOF to return multiple values. IF someone can help me on that subject, that will be great.</DIV>
<DIV>Thank you.</DIV>
<DIV> </DIV>
<DIV> </DIV><br clear=all><hr>Get more from the Web. FREE MSN Explorer download : <a href="http://explorer.msn.com">http://explorer.msn.com</a><br></p></html;
^ permalink raw reply [nested|flat] 8+ messages in thread
* SETOF
@ 2003-03-04 17:44 Fernando <fernando@icreativa.com.mx>
0 siblings, 0 replies; 8+ messages in thread
From: Fernando @ 2003-03-04 17:44 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am using Postgresql version 7.2.2
I made a small function...
CREATE FUNCTION ejem1(varchar) RETURNS SETOF to varchar as'
SELECT names from mi_tabla WHERE city = $1; '
language ' SQL ';
ejem1
------------
Sergio
Carlos
Fernando
When wanting to obtain several columns I do this...
CREATE FUNCTION ejem2(varchar) RETURNS SETOF mi_tabla as'
SELECT * from mi_tabla WHERE city = $1;'
language ' SQL ';
ejem2
---------------
137956448
137956448
137956448
The number of registries that return is the correct, the question is, because
it does not return the fields of the table, and that is what in its place
this showing to me...
Greetings and thank you very much!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SETOF
@ 2003-03-05 11:47 Christoph Haller <ch@rodos.fzk.de>
2003-03-05 12:47 ` Re: SETOF Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 8+ messages in thread
From: Christoph Haller @ 2003-03-05 11:47 UTC (permalink / raw)
To: pgsql-sql; +Cc: fernando@icreativa.com.mx
>
> CREATE FUNCTION ejem1(varchar) RETURNS SETOF to varchar as'=20
> SELECT names from mi_tabla WHERE city =3D $1; '=20
> language ' SQL ';=20
>
> ejem1=20
> ------------
> Sergio=20
> Carlos=20
> Fernando=20
>
> When wanting to obtain several columns I do this...=20
>
> CREATE FUNCTION ejem2(varchar) RETURNS SETOF mi_tabla as'=20
> SELECT * from mi_tabla WHERE city =3D $1;'=20
> language ' SQL ';=20
>
> ejem2=20
> ---------------
> 137956448=20
> 137956448=20
> 137956448=20
>
> The number of registries that return is the correct, the question is,
becau=
> se=20
> it does not return the fields of the table, and that is what in its
place=
> =20
> this showing to me...=20
> Greetings and thank you very
much!=20=20=20=20=20=20=20=20=20=20=20=20=20=
> =20=20
>
You cannot obtain several columns from SQL functions.
See the list archives (search for SETOF) for details.
You have to use e.g. a plpgsql function instead.
Regards, Christoph
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SETOF
2003-03-05 11:47 Re: SETOF Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-05 12:47 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 8+ messages in thread
From: Greg Stark @ 2003-03-05 12:47 UTC (permalink / raw)
To: pgsql-sql
Christoph Haller <ch@rodos.fzk.de> writes:
> You cannot obtain several columns from SQL functions.
> See the list archives (search for SETOF) for details.
> You have to use e.g. a plpgsql function instead.
How do you do it with a plpgsql function?
FWIW, this is probably the thing I wish for the most often. I can't count how
many times I've wanted to be able to write queries like:
select *,
(select x,y,z from b where b.id = a.foo)
(select xx,yy,zz from c where c.id = a.bar)
from a
As it is I either have to find a way to turn it into a join, which isn't
always possible, or duplicate the subquery which can cause multiple identical
table lookups.
I suppose one alternative would be for postgres to check if there are multiple
subplans to see if the subplan is the same except for the returned columns and
merge them somehow.
That doesn't solve the case of when you want a function that returns two
columns though.
--
greg
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SETOF
@ 2003-03-05 13:11 Christoph Haller <ch@rodos.fzk.de>
2003-03-05 13:36 ` Re: SETOF Greg Stark <gsstark@mit.edu>
2003-03-05 13:37 ` Re: SETOF Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 8+ messages in thread
From: Christoph Haller @ 2003-03-05 13:11 UTC (permalink / raw)
To: pgsql-sql; +Cc: fernando@icreativa.com.mx; gsstark@mit.edu
>
>
> Christoph Haller <ch@rodos.fzk.de> writes:
>
> > You cannot obtain several columns from SQL functions.
> > See the list archives (search for SETOF) for details.
> > You have to use e.g. a plpgsql function instead.
>
> How do you do it with a plpgsql function?
>
>
Sorry, Fernando, I was wrong. It works from version 7.3.
The reason why it's not working on your machine is probably
because of a prior version.
And Greg, refer to
http://techdocs.postgresql.org/guides/SetReturningFunctions
I think it will fit your needs.
Regards, Christoph
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SETOF
2003-03-05 13:11 Re: SETOF Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-05 13:36 ` Greg Stark <gsstark@mit.edu>
1 sibling, 0 replies; 8+ messages in thread
From: Greg Stark @ 2003-03-05 13:36 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql; fernando@icreativa.com.mx; gsstark@MIT.EDU
Christoph Haller <ch@rodos.fzk.de> writes:
> >
> >
> > Christoph Haller <ch@rodos.fzk.de> writes:
> >
> > > You cannot obtain several columns from SQL functions.
> > > See the list archives (search for SETOF) for details.
> > > You have to use e.g. a plpgsql function instead.
> >
> > How do you do it with a plpgsql function?
>
> And Greg, refer to
> http://techdocs.postgresql.org/guides/SetReturningFunctions
>
> I think it will fit your needs.
That document describes using the function in the from list of the select.
That's equivalent to converting the query to a join, which isn't always
possible or convenient. I was talking about using a subquery or function in
the output column list to produce multiple output columns.
I'm sorry I may have muddied the waters in that it sounds like my comment
wasn't about exactly the same issue as Chistoph's.
--
greg
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SETOF
2003-03-05 13:11 Re: SETOF Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-05 13:37 ` Greg Stark <gsstark@mit.edu>
1 sibling, 0 replies; 8+ messages in thread
From: Greg Stark @ 2003-03-05 13:37 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql; fernando@icreativa.com.mx; gsstark@MIT.EDU
> I'm sorry I may have muddied the waters in that it sounds like my comment
> wasn't about exactly the same issue as Chistoph's.
Oops, apologies, I got the names mixed up.
--
greg
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SETOF
@ 2003-03-05 14:10 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 8+ messages in thread
From: Christoph Haller @ 2003-03-05 14:10 UTC (permalink / raw)
To: pgsql-sql; +Cc: gsstark@mit.edu
>
> That document describes using the function in the from list of the
select.
> That's equivalent to converting the query to a join, which isn't
always
> possible or convenient. I was talking about using a subquery or
function in
> the output column list to produce multiple output columns.
>
I see your point. But, why wouldn't this work in the output column list
too?
The new return form "return next" looks like it could be done.
I'm still hanging around on 7.2, so I can't figure out if it's working
now.
Regards, Christoph
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-03-05 14:10 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-29 23:35 SETOF Limin Shen <limin_shen@hotmail.com>
2003-03-04 17:44 SETOF Fernando <fernando@icreativa.com.mx>
2003-03-05 11:47 Re: SETOF Christoph Haller <ch@rodos.fzk.de>
2003-03-05 12:47 ` Greg Stark <gsstark@mit.edu>
2003-03-05 13:11 Re: SETOF Christoph Haller <ch@rodos.fzk.de>
2003-03-05 13:36 ` Greg Stark <gsstark@mit.edu>
2003-03-05 13:37 ` Greg Stark <gsstark@mit.edu>
2003-03-05 14:10 Re: SETOF Christoph Haller <ch@rodos.fzk.de>
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