[SQL] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Calling SQL functions that return sets
On Mon, 1 Aug 2005, Tom Lane wrote: > Chris Mungall <[EMAIL PROTECTED]> 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 > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Common patterns for 'store' functions
I find store functions fairly useful; eg for any table 'foo', the function store_foo(v1,v2,...,vn) returns int; will perform an insert (if a unique key is not present) or an update (if the unique key is present) v1,...,vn may be values for columns in the table foo, or perhaps also for tables foo_a, foo_b, c_foo related to foo by foreign keys (in a highly normalised db, it can be convenient to combine these multiple update/inserts into a single function call). These functions can be implemented in an application/middleware layer (eg perl,java,..) or as pgsql functions. Both approaches have their strengths and weaknesses. For my purposes, sometimes pgsql is preferable. For one thing, pgsql store functions will be faster since it involves less client-server I/O. However, coding these can be fairly tedious. There is the additional problem that it's generally desirable to provide multiple signatures for different permutations of v1,...,vn. This can be unwieldy when a store function has lots of possible values. In these cases, the application layer approach has a big advantage, as the arguments to a function call generally don't have to be position dependent. As far as I am aware, there is no equivalent way to do this in plpgsql. Is this a common use case, or do people typically do this in the application/middleware layer? If other people are doing this, are there any common design patterns they would like to share? What about code that helps auto-generate some of these functions? Or even super-clever middleware that can decide whether to do it in the application layer or autogenerate some helper functions...? Cheers Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] R-tree and start/end queries
On Wed, 21 Sep 2005, Sean Davis wrote: > I have a table like: > > Create table gf ( > pkserial, > start int, > end int, > gfvarchar > ); > > I want to do queries along the lines of: > > "find all gf that overlap with (1,2)" or > "find all gf that overlap with each other" > > And others. I have read over the documentation, but I still remain unclear > about how to implement R-tree indexing in this situation. Any suggestions? Hi Sean I'm guessing that this is for some kind of genome database, yep? You may want to look at the chado database which has a growing library of functions for this sort of thing; www.gmod.org/schema Here is the code for doing range interval functions; our featureloc is equivalent to your "gf" (though we separate the entity from the entity being located). Our fmin and fmax may be equivalent to your start and end above (unless you indicate directionality with start>end in which case the intersection functions get a bit trickier). Our feature_id is probably equivalent to your gf column. We use the builtin pg types "point" and "box" and make an RTREE index over this: -- -- functions operating on featureloc ranges -- -- create a point CREATE OR REPLACE FUNCTION create_point (int, int) RETURNS point AS 'SELECT point ($1, $2)' LANGUAGE 'sql'; -- create a range box -- (make this immutable so we can index it) CREATE OR REPLACE FUNCTION boxrange (int, int) RETURNS box AS 'SELECT box (create_point(0, $1), create_point($2,5))' LANGUAGE 'sql' IMMUTABLE; -- create a query box CREATE OR REPLACE FUNCTION boxquery (int, int) RETURNS box AS 'SELECT box (create_point($1, $2), create_point($1, $2))' LANGUAGE 'sql' IMMUTABLE; --functional index that depends on the above functions CREATE INDEX binloc_boxrange ON featureloc USING RTREE (boxrange(fmin, fmax)); CREATE OR REPLACE FUNCTION featureloc_slice(int, int) RETURNS setof featureloc AS 'SELECT * from featureloc where boxquery($1, $2) @ boxrange(fmin,fmax)' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION featureloc_slice(varchar, int, int) RETURNS setof featureloc AS 'SELECT featureloc.* FROM featureloc INNER JOIN feature AS srcf ON (srcf.feature_id = featureloc.srcfeature_id) WHERE boxquery($2, $3) @ boxrange(fmin,fmax) AND srcf.name = $1 ' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION featureloc_slice(int, int, int) RETURNS setof featureloc AS 'SELECT * FROM featureloc WHERE boxquery($2, $3) @ boxrange(fmin,fmax) AND srcfeature_id = $1 ' LANGUAGE 'sql'; -- can we not just do these as views? CREATE OR REPLACE FUNCTION feature_overlaps(int) RETURNS setof feature AS 'SELECT feature.* FROM feature INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) INNER JOIN featureloc AS y ON (y.feature_id=$1) WHERE x.srcfeature_id = y.srcfeature_idAND ( x.fmax >= y.fmin AND x.fmin <= y.fmax ) ' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION feature_disjoint_from(int) RETURNS setof feature AS 'SELECT feature.* FROM feature INNER JOIN featureloc AS x ON (x.feature_id=feature.feature_id) INNER JOIN featureloc AS y ON (y.feature_id=$1) WHERE x.srcfeature_id = y.srcfeature_idAND ( x.fmax < y.fmin OR x.fmin > y.fmax ) ' LANGUAGE 'sql'; Cheers Chris > Thanks, > Sean > > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 6: explain analyze is your friend
