FILTER clause for non-aggregate window functions

Андрей Жиденков <[email protected]>
Newsgroups gmane.comp.db.postgresql.sql
Message-ID <CAN=gQ4AG_XxuTSq2brj3GY_MK-Qc+KwnETQiMXGmycsgy_Mbhw@mail.gmail.com>
I need to find the first date value in widow which meets certain
conditions. In my case I use min() function with CASE like this:

SELECT min(CASE WHEN <conditions> THEN <date_field> end) OVER (PARTITION BY
.. ORDER BY <date_field> ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)

But this is too slow mainly because of min() should read all tuples in
window, I guess. So I tried to use first_value() function like this:

SELECT first_value(<date_field>) FILTER(WHERE <conditions>) OVER (PARTITION
BY .. ORDER BY <date_field> ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED
FOLLOWING)

But I got this error:

FILTER is not implemented for non-aggregate window functions

Why FILTER is not implemented for non-aggregate functions? Is there some
restrictions in PostgreSQL executor core or maybe this behavior will lead
to some conflicts? Is there a way to find needed value without scanning all
tuples in window? Any help would be greatly appreciated.

-- 
Andrey Zhidenkov
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.