To: pgsql-sql@postgresql.org
Subject: Re: SETOF
References: <3E65E3C5.1D964F0D@rodos.fzk.de>
In-Reply-To: <3E65E3C5.1D964F0D@rodos.fzk.de>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 05 Mar 2003 07:47:50 -0500
Message-ID: <8765qy9dzd.fsf@stark.dyndns.tv>
Lines: 31
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


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