Re: Include result of function call within WHERE clause in results

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
>