Re: [HACKERS] Question regarding new windowing functions in 8.4devel

On Fri, Jan 16, 2009 at 12:41:59PM -0500, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > We don't appear to be able to use the actual thing in the target list
> > either.
>
> Would you translate that into English? Or at least an example without
> trivial syntax errors?

This works:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WHERE
typ < 4
WINDOW w AS (partition by typ order by ts desc);

This doesn't:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
WHERE
rank() over w < 4
WINDOW w AS (partition by typ order by ts desc);

ERROR: window functions not allowed in WHERE clause
LINE 8: rank() over w < 4

This doesn't either, going with a "windows are like aggregates" theory:

SELECT
typ,
ts,
rank() over w AS foo_rank
FROM
foo
HAVING
rank() over w < 4
WINDOW w AS (partition by typ order by ts desc);
ERROR: column "foo.typ" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 2: typ,
^

Basically, there is no way I've found so far to qualify any window
function in the target list, which makes a giant POLA violation. With
any item in the target list other than a window function, it's
possible to qualify it either in the WHERE clause for non-aggregates
or in the HAVING clause for aggregates.

While we probably don't want to open the "qualify by alias" can of
worms, we might want to make it at least possible to add qualifiers to
window functions short of CTEs/subselects.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate