Re: Calling SQL functions that return sets

Chris Mungall <cjm@fruitfly.org> writes:
> On Mon, 1 Aug 2005, Tom Lane wrote:
>> Chris Mungall <cjm@fruitfly.org> writes:
>>> What are the reasons for deprecating the use of the function in the
>>> SELECT clause?
>> 
>> The semantics of having more than one set-returning function in the
>> target list are, um, poorly thought out.  However, we haven't removed
>> the feature because (as you note) there are things you can't do any
>> other way.

> Is there any roadmap for how this will be handled in future versions?

I think it's reasonably safe to say that we won't remove the feature for
at least one or two releases after having a 100% substitute (which the
present SRF-in-FROM feature is not, as you know).  There has been some
speculation that the SQL:2003 LATERAL syntax might offer an adequate
substitute, but no one is really working on that yet AFAIK.

> Coming back to earth, I have a more specific question which follows on
> from my initial question. If I have a function 'foo' which takes one
> argument and returns a setof some table or composite type, it seems I am
> unable to call the function from the select clause.

>  SELECT foo(1,2);
>  ERROR:  set-valued function called in context that cannot accept a set

The present plpgsql implementation only works for SRF-in-FROM.  (Which
is something that could probably be fixed, but given the development
direction we want to go in, it doesn't seem like a very good use of
time...)  You can do SRF-in-target-list with SQL-language functions or
C-coded functions; I'm not certain offhand about the status of the
other PL languages.
        regards, tom lane