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