Thread: Function call order dependency
Is there a knowable order in which functions are called within a query in PostgreSQL? For example I'll use the Oracle "contains" function, though this is not exactly what I'm doing, it just illustrates the issue clearly. select *, score(1) from mytable where contains(mytable.title, 'Winding Road', 1) order by score(1); The "contains" function does a match against mytable.title for the term 'Winding Road' and both returns and saves an integer score which may be retrieved later using the "score(...)" function. The integer used as a parameter in score(...) and contains(...) is an index to reference which score you need as more than one contains(...) call may be used in single query. This sets up an interesting issue, how can one ensure that "contains()" is called prior to any "score()" function on each row? Is this possible? Is there a specific order on which you can count? Would it be something like: "where" clause first, left to right, followed by select terms, left to right, and lastly the "order by" clause?
pgsql@mohawksoft.com writes:
> For example I'll use the Oracle "contains" function, though this is not
> exactly what I'm doing, it just illustrates the issue clearly.
> select *, score(1) from mytable where contains(mytable.title, 'Winding
> Road', 1) order by score(1);
> The "contains" function does a match against mytable.title for the term
> 'Winding Road' and both returns and saves an integer score which may be
> retrieved later using the "score(...)" function.
This is just a bad, bad idea. Side-effects in a WHERE-clause function
are guaranteed to cause headaches. When (not if) it breaks, you get
to keep both pieces.
regards, tom lane
pgsql@mohawksoft.com wrote: >Would it be something like: "where" clause first, left to right, followed >by select terms, left to right, and lastly the "order by" clause? I don't know what ANSI says, nor do I know what PostgreSQL exactly does at the moment, but, the only thing you can reasonably count on is that the WHERE clause is evaluated before the SELECT-result-rows and the ORDER BY clause (in any SQL database). You cannot depend on any left to right order, and you cannot depend on ORDER BY being evaluated after the SELECT-result-rows. -- Sincerely, Stephen R. van den Berg. "<Clarions sounding> *No one* expects the Spanish inquisition!"
> pgsql@mohawksoft.com writes: >> For example I'll use the Oracle "contains" function, though this is not >> exactly what I'm doing, it just illustrates the issue clearly. > >> select *, score(1) from mytable where contains(mytable.title, 'Winding >> Road', 1) order by score(1); > >> The "contains" function does a match against mytable.title for the term >> 'Winding Road' and both returns and saves an integer score which may be >> retrieved later using the "score(...)" function. > > This is just a bad, bad idea. Side-effects in a WHERE-clause function > are guaranteed to cause headaches. When (not if) it breaks, you get > to keep both pieces. I was kind of afraid of that. So, how could one implement such a function set?
> I was kind of afraid of that. So, how could one implement such a function > set? Write a function (say, score_contains) that returns NULL whenever contains would return false, and the score otherwise. SELECT * FROM ( SELECT *, score_contains(mytable.title, 'Winding Road', 1) AS score FROM mytable ) x WHERE x.score IS NOT NULL ORDER BY x.score ...Robert
> pgsql@mohawksoft.com writes: >> For example I'll use the Oracle "contains" function, though this is not >> exactly what I'm doing, it just illustrates the issue clearly. > >> select *, score(1) from mytable where contains(mytable.title, 'Winding >> Road', 1) order by score(1); > >> The "contains" function does a match against mytable.title for the term >> 'Winding Road' and both returns and saves an integer score which may be >> retrieved later using the "score(...)" function. > > This is just a bad, bad idea. Side-effects in a WHERE-clause function > are guaranteed to cause headaches. When (not if) it breaks, you get > to keep both pieces. Well, I guess I need to alter the question a bit. I need to perform an operation during query time and there are multiple results based on the outcome. For instance: (Lets try this) select myrank(t1.column1, t2.column2, 1) as rank, myscore(t1.column1,t2.column2, 1) as score from t1, t2 where myrank(t1.column1,t2.column2) > 10 order by myscore(t1.column1, t2.column2, 1) desc; This is a bit messier, and I wanted to resist this approach as it is ugly. The underlying code will check the values of the first and second parameters and only perform the operation if a previous call did not already act on the current parameters. Now, can I assume that in the above select statement, that each permutation of t1.column1 and t2.column2 will only be evaluated once and that myscore(...) and myrank(...) will all be called before the next permutation is evaluated? So, basically, I don't want to recalculate the values for each and every function call as that would make the system VERY slow.
pgsql@mohawksoft.com writes:
> I need to perform an operation during query time and there are multiple
> results based on the outcome. For instance: (Lets try this)
> select myrank(t1.column1, t2.column2, 1) as rank,
> myscore(t1.column1,t2.column2, 1) as score from t1, t2 where
> myrank(t1.column1,t2.column2) > 10 order by myscore(t1.column1,
> t2.column2, 1) desc;
Why not have one function that produces multiple output columns?
> Now, can I assume that in the above select statement, that each
> permutation of t1.column1 and t2.column2 will only be evaluated once and
> that myscore(...) and myrank(...) will all be called before the next
> permutation is evaluated?
You can assume that functions in the SELECT target list are evaluated
exactly once per output row (at least as long as no
SRFs-in-the-targetlist are involved). I don't think it'd be wise to
assume anything about order of evaluation, though it's probably true
that it's left-to-right at the moment.
regards, tom lane
>> I was kind of afraid of that. So, how could one implement such a >> function >> set? > > Write a function (say, score_contains) that returns NULL whenever > contains would return false, and the score otherwise. > > SELECT * FROM ( > SELECT *, score_contains(mytable.title, 'Winding Road', 1) AS > score FROM mytable > ) x WHERE x.score IS NOT NULL > ORDER BY x.score That could work, and while it fits my example, my actual need is a bit more complex. For instance, say I have two variables (I actually have a few that I need) select myvar1(1), myvar2(1), myvar3(1) from mytable where myfunction(mytable.column, 'some text to search for', 1) > 2; How could I ensure that (1) "myfunction" is called prior to myvar1(), myvar2(), and myvar3()? I think the answer is that I can't. So, the obvious solution is to pass all the variables to all the functions and have it first come first served. The next issue is something like this: select *, myvar1(t1.col1,t2.col2,1), myvar2(t1.col1.t2.col2,1) from t1,t2 where myfunction(t1.col1,t2.col2,1) > 10 order by myvar3(t1.col1,t2.col2,1) desc; Using a "first come first served" strategy, is there any discontinuity between the function calls for t1.col1 and t2.col2. Will they all be called for a particular combination of t1.col1 and t2.col2, in some unpredictable order before the next row(s) combination is evaluated or will I have to execute the underlying algorithm for each and every call? > > ...Robert >
> pgsql@mohawksoft.com writes: >> I need to perform an operation during query time and there are multiple >> results based on the outcome. For instance: (Lets try this) > >> select myrank(t1.column1, t2.column2, 1) as rank, >> myscore(t1.column1,t2.column2, 1) as score from t1, t2 where >> myrank(t1.column1,t2.column2) > 10 order by myscore(t1.column1, >> t2.column2, 1) desc; > > Why not have one function that produces multiple output columns? I was sort of trying to make this a fairly "generic" SQL extension who's methodology could be moved to other databases if needed. I guess multiple columns could work. I've got some code in another extension that does that. > >> Now, can I assume that in the above select statement, that each >> permutation of t1.column1 and t2.column2 will only be evaluated once and >> that myscore(...) and myrank(...) will all be called before the next >> permutation is evaluated? > > You can assume that functions in the SELECT target list are evaluated > exactly once per output row (at least as long as no > SRFs-in-the-targetlist are involved). I don't think it'd be wise to > assume anything about order of evaluation, though it's probably true > that it's left-to-right at the moment. But are all the items targeted in close proximity to each other BEFORE moving on to the next row? What about the "where" clause? would that be called out of order of the select target list? I'm doing a fairly large amount of processing and doing it once is important. /