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