Thread: functions in WHERE clause
Hi All, I've got one of these: SELECT * from some_table WHERE test_for_equality_is_syntactically_ugly; What I'd like to do is encapsulate the WHERE clause in a function, but I'm having no end of trouble. The WHERE clause expects the function to return a boolean value. I can certainly return a boolean value from a function, but here it seems to me that what the function really has to do is return a set of boolean values -- the test in the WHERE clause sometimes evaluates to true and sometimes evaluates to false, and that is in turn used to constrain the query results. But you can't return a set of anything (I don't think) in a WHERE clause, because it seems to want a singular boolean value. Is it possible to do what I'm trying to do? I've written a few simple sql and pl/pgsql functions over the years, but I'm no expert. Perhaps I'm barking up the wrong tree here. It seems like what I really need is a way to have some kind of rewrite rule (e.g. when you see "foo(x)" substitute ugly string). Sort of like a Lisp macro ;) Steve -- Stephen Ramsay Assistant Professor Department of English University of Georgia email: sramsay@uga.edu web: http://cantor.english.uga.edu/ PGP Public Key ID: 0xA38D7B11
On Sun, Mar 05, 2006 at 10:16:52AM -0500, sramsay@uga.edu wrote: > I've got one of these: > > SELECT * from some_table WHERE > test_for_equality_is_syntactically_ugly; > > What I'd like to do is encapsulate the WHERE clause in a function, > but I'm having no end of trouble. Would a view work? If not then please provide a more concrete example that shows what you're trying to do. CREATE VIEW foo AS SELECT * FROM some_table WHERE test_for_equality_is_syntactically_ugly; -- Michael Fuhr
On Sun, Mar 05, 2006 at 10:26:35AM -0700, Michael Fuhr wrote: > On Sun, Mar 05, 2006 at 10:16:52AM -0500, sramsay@uga.edu wrote: > > I've got one of these: > > > > SELECT * from some_table WHERE > > test_for_equality_is_syntactically_ugly; > > > > What I'd like to do is encapsulate the WHERE clause in a function, > > but I'm having no end of trouble. > > Would a view work? If not then please provide a more concrete > example that shows what you're trying to do. > > CREATE VIEW foo AS > SELECT * FROM some_table > WHERE test_for_equality_is_syntactically_ugly; Thanks for reply! No, because test_for_equality_is_syntactically_ugly varies. Here's the specifics: I've written an XML database (of sorts) that uses the ltree contrib module to find elements that belong to particular nodes. Right now, if you wanted to find all the word tokens found within the titleStmt element of a set of XML documents, you'd do something like this: SELECT token FROM event WHERE ltree ~ '*.titleStmt.*'; (ltree contains a set of "paths" that together express the hierarchical relationships in the documents, so you can format that bit after the ~ to include any element, and you can also use other kinds of operators to get ancestors, children, etc.). Now, I realize this isn't *really* all that syntactically ugly, but it would be really nice if the user of the db could type in an XPath like so: SELECT token FROM event WHERE xpath("//titleStmt"); This will require some munging inside a function to go from the XPath to the ltree-style regex expression, but I'm finding that I'm having more basic troubles. You can't do this: CREATE FUNCTION xpath(lquery) RETURNS ltree AS $$ SELECT ltree FROM event WHERE ltree ~ $1; $$ LANGUAGE SQL; Because that only returns the first value from the SELECT (and it's not pl-pgsql anyway). But I also can't get this kind of thing to work: CREATE FUNCTION xpath(lquery) RETURNS SETOF ltree AS $$ DECLARE tree record; BEGINFOR tree IN SELECT ltree FROM event WHERE ltree ~ $1 LOOP RETURN NEXT tree;END LOOP;RETURN; END $$ LANGUAGE plpgsql; Because SETOF won't work in a WHERE context. Part of the problem is that I don't really understand how WHERE constraints work under the hood. The lvalue in the WHERE clause expects a boolean, but I think that really means "evaluate the truth or falsehood of this expression on all rows in the table specified in the FROM clause." So how do you get a function to play nicely with that concept? "The argument of WHERE must not return a set," as the psql shell keeps telling me, but is there anything it would accept that would make this work? Thanks again, Steve -- Stephen Ramsay Assistant Professor Department of English University of Georgia email: sramsay@uga.edu web: http://cantor.english.uga.edu/ PGP Public Key ID: 0xA38D7B11
sramsay@uga.edu writes: > I've got one of these: > SELECT * from some_table WHERE > test_for_equality_is_syntactically_ugly; > What I'd like to do is encapsulate the WHERE clause in a function, You mean like replacing SELECT * from some_table WHERE x = 42 AND y = 77 with create function mytest(int,int) returns bool as $$select $1 = 42 AND $2 = 77$$ language sql; SELECT * from some_table WHERE mytest(x,y); A SQL-language function like this should get inlined into the query, so that you don't lose any performance compared to writing out the full expression each time. regards, tom lane
sramsay@uga.edu writes: > You can't do this: > CREATE FUNCTION xpath(lquery) RETURNS ltree AS $$ > SELECT ltree FROM event WHERE ltree ~ $1; > $$ LANGUAGE SQL; That would work fine if you said RETURNS SETOF ltree. > But I also can't get this kind of thing to work: > CREATE FUNCTION xpath(lquery) RETURNS SETOF ltree AS $$ > DECLARE > tree record; > BEGIN > FOR tree IN SELECT ltree FROM event WHERE ltree ~ $1 LOOP > RETURN NEXT tree; > END LOOP; > RETURN; > END > $$ LANGUAGE plpgsql; That should work too, except that you are trying to return a record not an ltree value. Try "RETURN NEXT tree.ltree". > Because SETOF won't work in a WHERE context. Possibly you need to read the error messages you are getting more closely, because I'm pretty sure whatever it said had nothing to do with either SETOF or WHERE ... regards, tom lane
On Sun, Mar 05, 2006 at 01:16:40PM -0500, Tom Lane wrote: > sramsay@uga.edu writes: > That would work fine if you said RETURNS SETOF ltree. > > That should work too, except that you are trying to return a record > not an ltree value. Try "RETURN NEXT tree.ltree". > > > Because SETOF won't work in a WHERE context. > > Possibly you need to read the error messages you are getting more > closely, because I'm pretty sure whatever it said had nothing to > do with either SETOF or WHERE ... I think it does, actually. I can write functions that return ltrees, records, or sets of ltree, and they'll work in any part of the query -- except the WHERE clause. If the function returns anything other than a bool, it complains that the function must return a bool. Which makes sense to me, actually, because the "result" of something like: x = 42 and y = 77 (to quote your earlier example) should be true or false -- not a set of rows or records or types. At least I think. But let's return to your example for a moment, because it really does look like the kind of "rewrite" rule that I want here. You suggested replacing: SELECT * from some_table WHERE x = 42 AND y = 77 with create function mytest(int,int) returns bool as $$select $1 = 42 AND $2 = 77$$ language sql; So you could then do: SELECT * from some_table WHERE mytest(x,y); But imagine instead that this function is more generic. You know that you're trying to get something that's equal to x and equal to y, but you don't know (until the function is called) what those rvalues should be. In other words, it's the 42 and the 47 that you don't know until runtime -- you always know what columns your searching on. When I try do something along the lines of what you're doing, I get: CREATE OR REPLACE FUNCTION xpath(lquery) RETURNS bool AS $$SELECT ltree ~ $1; $$ LANGUAGE SQL; But I can't even load that function, because it says: ERROR: column "ltree" does not exist And round and round I go . . . Thanks for the reply, Steve -- Stephen Ramsay Assistant Professor Department of English University of Georgia email: sramsay@uga.edu web: http://cantor.english.uga.edu/ PGP Public Key ID: 0xA38D7B11
On Sun, 5 Mar 2006 sramsay@uga.edu wrote: > On Sun, Mar 05, 2006 at 01:16:40PM -0500, Tom Lane wrote: > > sramsay@uga.edu writes: > > That would work fine if you said RETURNS SETOF ltree. > > > > That should work too, except that you are trying to return a record > > not an ltree value. Try "RETURN NEXT tree.ltree". > > > > > Because SETOF won't work in a WHERE context. > > > > Possibly you need to read the error messages you are getting more > > closely, because I'm pretty sure whatever it said had nothing to > > do with either SETOF or WHERE ... > > I think it does, actually. I can write functions that return > ltrees, records, or sets of ltree, and they'll work in any part of > the query -- except the WHERE clause. If the function returns > anything other than a bool, it complains that the function must > return a bool. > > Which makes sense to me, actually, because the "result" of something > like: > > x = 42 and y = 77 > > (to quote your earlier example) should be true or false -- not a set > of rows or records or types. At least I think. > > But let's return to your example for a moment, because it really > does look like the kind of "rewrite" rule that I want here. > > You suggested replacing: > > SELECT * from some_table WHERE x = 42 AND y = 77 > > with > > create function mytest(int,int) returns bool as > $$select $1 = 42 AND $2 = 77$$ language sql; > > So you could then do: > > SELECT * from some_table WHERE mytest(x,y); > > But imagine instead that this function is more generic. You know > that you're trying to get something that's equal to x and equal to > y, but you don't know (until the function is called) what those > rvalues should be. In other words, it's the 42 and the 47 that you > don't know until runtime -- you always know what columns your > searching on. Then you need to pass those in as well as something representing the row that's being tested -- where clauses are filters on rows. The whole row representation might be better than columns for some cases. For example: create table tt1(a int, b int); create function f1(tt1, int) returns bool as 'select $1.a = $2' language 'sql'; select * from tt1 where f1(tt1, 1); --- The other option is to do this as a set returning function in the first place rather than trying to do a wierd where clause thing. create function f2(int) returns setof tt1 as 'select * from tt1 where a = $1' language 'sql'; select * from f2(1);
On Sun, Mar 05, 2006 at 11:38:46AM -0800, Stephan Szabo wrote: > > But imagine instead that this function is more generic. You know > > that you're trying to get something that's equal to x and equal to > > y, but you don't know (until the function is called) what those > > rvalues should be. In other words, it's the 42 and the 47 that you > > don't know until runtime -- you always know what columns your > > searching on. > > Then you need to pass those in as well as something representing the row > that's being tested -- where clauses are filters on rows. The whole row > representation might be better than columns for some cases. > > For example: > > create table tt1(a int, b int); > create function f1(tt1, int) returns bool as 'select $1.a = $2' language > 'sql'; > > select * from tt1 where f1(tt1, 1); Eureka! That does it: CREATE OR REPLACE FUNCTION xpath(event, lquery) RETURNS bool AS $$ SELECT $1.ltree ~ $2; $$ LANGUAGE SQL; And it's fast (the event table is a view)! Interestingly, this version is very slow: CREATE OR REPLACE FUNCTION xpath(event, lquery) RETURNS bool AS $$ BEGIN RETURN $1.ltree ~ $2; END $$ LANGUAGE plpgsql STABLE; EXPLAIN shows lots of sequential scans when you try to do it this way. No inlining, perhaps? I guess I am still a little confused as to why you have to pass the table in as a parameter -- why you can't just do: RETURN event.ltree ~ $2; But I'll live . . . Thanks to one and all for the very generous assistance. I have learned much. Steve > The other option is to do this as a set returning function in the first > place rather than trying to do a wierd where clause thing. Mostly for syntactic clarity on the caller's end, but I understand what you're saying. -- Stephen Ramsay Assistant Professor Department of English University of Georgia email: sramsay@uga.edu web: http://cantor.english.uga.edu/ PGP Public Key ID: 0xA38D7B11
On Sun, 5 Mar 2006 sramsay@uga.edu wrote: > On Sun, Mar 05, 2006 at 11:38:46AM -0800, Stephan Szabo wrote: > > > But imagine instead that this function is more generic. You know > > > that you're trying to get something that's equal to x and equal to > > > y, but you don't know (until the function is called) what those > > > rvalues should be. In other words, it's the 42 and the 47 that you > > > don't know until runtime -- you always know what columns your > > > searching on. > > > > Then you need to pass those in as well as something representing the row > > that's being tested -- where clauses are filters on rows. The whole row > > representation might be better than columns for some cases. > > > > For example: > > > > create table tt1(a int, b int); > > create function f1(tt1, int) returns bool as 'select $1.a = $2' language > > 'sql'; > > > > select * from tt1 where f1(tt1, 1); > > Eureka! That does it: > > CREATE OR REPLACE FUNCTION xpath(event, lquery) RETURNS bool AS $$ > SELECT $1.ltree ~ $2; > $$ LANGUAGE SQL; > > And it's fast (the event table is a view)! > > Interestingly, this version is very slow: > > CREATE OR REPLACE FUNCTION xpath(event, lquery) RETURNS bool AS > $$ > BEGIN > RETURN $1.ltree ~ $2; > END > $$ LANGUAGE plpgsql STABLE; > > EXPLAIN shows lots of sequential scans when you try to do it this > way. No inlining, perhaps? Yeah, I'd assume it's running the function once for each row after the construction of the event row (so it's probably doing any joins in the view first), whereas the SQL one might inline and be optimized differently which might shrink the number of rows that any joins are being done on. > I guess I am still a little confused as to why you have to pass the > table in as a parameter -- why you can't just do: > > RETURN event.ltree ~ $2; Well, the problem here is say you have a function f(int) that says RETURN tt1.a = $1 like my example. What does "select * from tt2 where f(1)" or "select * from tt1 a, tt1 b where f(1)" mean? Functions don't really have much context sensitivity, and it's not always reasonable to expect to be able to open the box to know things like f(x) in the above depends on there being a single tt1 entry in the from clause (and trying to deal with outer references would be an utter mess).
Tom Lane <tgl@sss.pgh.pa.us> writes: > A SQL-language function like this should get inlined into the query, > so that you don't lose any performance compared to writing out the > full expression each time. I think what's going on here is that he doesn't really want a function in the programming sense. A function takes arguments and returns a result based on those arguments. It would be trivial to make a function that returns true or false for his constraints but it would require passing in the columns as well as the values he's testing against. So it would be "xpath(ltreecolumn, 42, 47)". What he's looking for is a convenience macro that hides the columns he's testing against so he doesn't have to write the columns in every query. So he can just type "xpath(42,47)" without retyping "ltreecolumn". Afaik there's no functionality for this in Postgres. functions aren't given any context information when they're executed aside from the arguments passed. If I understand what you want then I think you're much better off just typing the name of the column you're testing against explicitly every time anyways. One day you'll need more flexibility or you'll have someone else reading the code and you'll be glad you're not hiding what's going on and hard coding column names inside some macro function anyways. -- greg
Hi, Steve, sramsay@uga.edu wrote: > SELECT * from some_table WHERE > test_for_equality_is_syntactically_ugly; > The WHERE clause expects the function to return a boolean value. I > can certainly return a boolean value from a function, but here it > seems to me that what the function really has to do is return a > set of boolean values -- the test in the WHERE clause sometimes > evaluates to true and sometimes evaluates to false, and that is in > turn used to constrain the query results. But you can't return a > set of anything (I don't think) in a WHERE clause, because it seems > to want a singular boolean value. And this is as it is intended. The equality test is applied row-by-row, and for each row, it is either true or false, but not undecided. So your query should look like SELECT * FROM some_table WHERE your_function(column_a, column_b); > Is it possible to do what I'm trying to do? I've written a few > simple sql and pl/pgsql functions over the years, but I'm no expert. Yes, it is. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org