Обсуждение: SETOF

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!

>
> 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

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

>
>
> 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

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

> 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

>
> 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