Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] 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! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] 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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function call order dependency
[EMAIL PROTECTED] 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. / -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
