Re: Letting a function return multiple columns instead of a single complex one

"A.j. Langereis" <a(dot)j(dot)langereis(at)inter(dot)nl(dot)net> writes:
> The problem I am facing is that I will execute this function as part of =
> another query where the parameter will be one of the columns of another =
> table. Something like: "select bar.*, get_a_foo(c) from bar". I need the =
> result set to be like a table, because I'll have to use it later in =
> another query.

Try something like

test=# select c,(ff).* from (select bar.*,get_a_foo(c) as ff from bar) b;
c | a | b
---+---+---
1 | 1 | 2
(1 row)

Not amazingly elegant, but it works. Note that you need to beware of
the possibility that the subselect will get flattened, leading to
multiple evaluations of your function. This doesn't happen in this
particular case because you declared the function as returning set,
but if you don't then you'll need additional countermeasures.

In general I'd suggest that this style of programming is forcing SQL to
do something SQL doesn't do very well, ie, emulate a functional
language. It's likely to end up both notationally ugly and very
inefficient. You should think hard about whether you can't express your
problem with views and joins instead.

regards, tom lane