Thread: Include result of function call within WHERE clause in results
Hi all - I need to select the rows of a table that satisfy a condition that includes a function call, and I want to include the result of the function call as a column in the resulting table. For example, this pseudo-code produces the result I'm looking for: SELECT field1, some_function(...) FROM my_table WHERE some_function(...) < some_number However, I would like to avoid calling the function in two places, especially since embedded in the parameters to the function I have another SELECT command. Not to mention it just looks messy... I've already tried the following without luck: SELECT field1, some_function(...) AS result FROM my_table WHERE result < some_number Is there a way to cache the result of the function call so it can be used in both places without two separate calls? Thanks in advance, Dallas Morisette
Hi Bastiaan - Both the WHERE and HAVING clauses filter the result set, but the HAVING clause is, I believe, for cases where you wish to filter on aggregate results, like sum or count, while the WHERE clause is for non-aggregated results. I'm not using an aggregating function, so I believe I need a solution that uses a WHERE clause. Thanks for your suggestion. Dallas On May 16, 2009, at 11:44 PM, Bastiaan Olij wrote: > Hi Dallas, > > Haven't tried this with functions so I don't know the exact syntax but > try adding a having clause. Having allows you to filter on your result > set. Obviously there are some performance limitations but with a > function you probably have that already. > > Hope that helps, > > Bastiaan Olij > > Dallas Morisette wrote: >> Hi all - >> >> I need to select the rows of a table that satisfy a condition that >> includes a function call, and I want to include the result of the >> function call as a column in the resulting table. For example, this >> pseudo-code produces the result I'm looking for: >> >> SELECT field1, some_function(...) FROM my_table >> WHERE some_function(...) < some_number >> >> However, I would like to avoid calling the function in two places, >> especially since embedded in the parameters to the function I have >> another SELECT command. Not to mention it just looks messy... >> >> I've already tried the following without luck: >> >> SELECT field1, some_function(...) AS result FROM my_table >> WHERE result < some_number >> >> Is there a way to cache the result of the function call so it can be >> used in both places without two separate calls? >> >> Thanks in advance, >> Dallas Morisette >> >
Thanks, Adam. That worked, and makes some sense as well. Basically create a temporary table with the function run on all elements including the result of the function as a new field, then filter based on that added column. If I'm limiting the set based on other conditions I can put them in the sub-select statement to minimize the number of function calls necessary. Regards. Dallas On May 17, 2009, at 4:56 AM, Adam Ruth wrote: > You can put the query into a sub-select: > > SELECT * from (SELECT field1, some_function() as field2 FROM > my_table) T where T.field2 < some_number > > > > > On 17/05/2009, at 10:44 AM, Dallas Morisette wrote: > >> Hi all - >> >> I need to select the rows of a table that satisfy a condition that >> includes a function call, and I want to include the result of the >> function call as a column in the resulting table. For example, this >> pseudo-code produces the result I'm looking for: >> >> SELECT field1, some_function(...) FROM my_table >> WHERE some_function(...) < some_number >> >> However, I would like to avoid calling the function in two places, >> especially since embedded in the parameters to the function I have >> another SELECT command. Not to mention it just looks messy... >> >> I've already tried the following without luck: >> >> SELECT field1, some_function(...) AS result FROM my_table >> WHERE result < some_number >> >> Is there a way to cache the result of the function call so it can >> be used in both places without two separate calls? >> >> Thanks in advance, >> Dallas Morisette >> >> -- >> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-novice >