Search code examples
sqlpostgresql-9.0

Postgres Aggregator which checks for occurrences

Does there exist a Postgres Aggregator such that, when used on the following table:

 id | value 
----+-----------
  1 |     1
  2 |     1
  3 |     2
  4 |     2
  5 |     3
  6 |     3
  7 |     3
  8 |     4
  9 |     4
 10 |     5

in a query such as:

select agg_function(4,value) from mytable where id>5

will return

agg_function
--------------
t

(a boolean true result) because a row or rows with value=4 were selected?

In other words, one argument specifies the value you are looking for, the other argument takes the column specifier, and it returns true if the column value was equal to the specified value for one or more rows?

I have successfully created an aggregate to do just that, but I'm wondering if I have just re-created the wheel...

Solution 
  • select sum(case when value = 4 then 1 else 0 end) > 0
from mytable
where id > 5