Thread: Calling SQL functions that return sets

Hi all,

I have a question on functions returning sets. I have a lot of complex
functionality I would like to keep in the DBMS rather than in an
application, and I would like this functionality to be available as
queries over standard SQL relations.

Section 31.4.4 of the docs
(http://www.postgresql.org/docs/8.0/static/xfunc-sql.html ) gives an
example of calling a function which returns a set, with the function in
the SELECT clause. It also states that this is deprecated, and that
functions returning sets should be placed in the FROM clause of the query.

However, unless I'm missing something it seems that arguments of a
FROM-clause function cannot be used in the SELECT or WHERE
clause. This seems to me a _huge_ limitation.

The example given in 31.4.4 is a function
 CREATE FUNCTION listchildren(text) RETURNS SETOF text AS ...

We can call it (using the deprecated syntax) like this:
 SELECT name, listchildren(name) FROM nodes;

(where nodes is some relation with a text column called "name")

I like this because I can implement a VIEW:
CREATE VIEW name2child AS SELECT name, listchildren(name) FROM nodes;

Of course, with the example given, it is easier to do this by directly
querying the nodes relation. However, I am interested in the more
general case whereby a relation can be implemented with an arbitrarily
complex procedural pl/pgsql (or some other language) function, yet
appear to have standard relational semantics.

If I am now forced to move the function call to the FROM clause, it
would appear to be impossible to implement the above view, or any
equivalent for a function returning a set. This means that all the
wonderful postgres machinery for defining complex functions is
absolutely useless to me if I wish to retain standard relational
semantics, and not have my code break with some future postgres
version.

What are the reasons for deprecating the use of the function in the
SELECT clause? Am I missing something obvious?

Thanks for any insight

--
Chris Mungall

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.
        regards, tom lane

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.

Would you recommend I press ahead and define views that make use of setof
function calling in the select clause? I would need to be reasonably
confident that if this is more strongly deprecated in future versions I
can at least recode my views (using some as-yet-to-be-defined function
definition and calling mechanism) and preserve their existing semantics.

Is there any roadmap for how this will be handled in future versions?
Understandably, this may not be a high priority.

I'm sure you and the other developers have already thought a lot about
this, but for what it's worth I'd like to pich in a vote for taking a
prolog-esque approach here. In prolog, everything is a relation. A
function taking two arguments is really just a 3-ary relation over
(arg1,arg2,result). Thus functions that produce more than one value (eg
sqrt) don't require any extensions to the relational model.

I amn't the slightest bit familair with the Pg internals, and there may be
some very good reasons that make this impractical.

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
SELECT mycol(foo(1,2));ERROR:  set-valued function called in context that cannot accept a set

It looks like I may be stuck anyway....

Cheers
Chris

>             regards, tom lane
>

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