Skip site navigation (1) Skip section navigation (2)

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

From: Dallas Morisette <p28flyer(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Include result of function call within WHERE clause in results
Date: 2009-05-17 14:12:55
Message-ID: 365F116A-AB82-4099-A830-9413169B76DD@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>>
>


In response to

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2009-05-17 14:15:38
Subject: Re: COUNT() BASED ON MULTIPLE WHERE CONDITIONS
Previous:From: ...tharasDate: 2009-05-17 13:05:56
Subject: COUNT() BASED ON MULTIPLE WHERE CONDITIONS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group