Re: Windowing Function Patch Review -> Standard Conformance

2008/12/20 Tom Lane <tgl@sss.pgh.pa.us>:
> I've been studying the grammar for the windowing patch a bit.  It seems
> to me that the <existing window name> option for <window specification>
> got left out.  I think that WindowDef needs to have two name fields,
> one for the name (if any) defined by the window definition, and one
> for the referenced window name if any.  Also the "OVER name" syntax
> seems like it maps to a referenced window name not a defined window
> name.

I completely missed this issue. If the <existing window name> is
allowed in <window clause>, then does it mean this is possible?

SELECT row_number() OVER w2 FROM t
WINDOW w1 AS (PARTITION BY grp), w2(w1)

And what if w1 refers to w2 and w2 refers to w1 cyclicly? And from
what I read the spec, it seems to me that it effects only frame clause
which is unlikely implemented for 8.4, because if <existing window
name) is specified then <partition clause> and <order by clause> are
both permitted in the window definition.

> I am also fairly strongly inclined to rip out all of the frame_clause
> syntax, since (1) it's unimplemented and unlikely to get implemented
> for 8.4, and (2) it's not particularly satisfactory anyway.  The
> frame_bound_const business has to be rethought, because it's ugly and
> it doesn't even satisfy the limited requirements of the spec (cf
> <general value specification> which is an allowed alternative for
> <unsigned value specification>).  It might be that we'll end up having
> to make BETWEEN be a fully reserved word in order to implement that
> syntax.

The reason I added those grammer was 1) there had been possibilities
to implement frame clause, and 2) to support not-implemented error. 1)
is unlikey to be done and for 2) we need only rows/range syntax. So if
it is annoying for 8.4 release, I don't disagree with your suggestion.


> BTW, I notice we also have not implemented the spec's bizarre
> RESPECT NULLS/IGNORE NULLS decoration for lead/lag or FROM FIRST/LAST
> for nth_value.  This is fine with me, but we probably ought to document
> workarounds for that, if possible.  I guess FROM FIRST/LAST could be
> handled by reversing the window sort order; what about the NULLS stuff?
>
For function-specific syntax seems like more big discussion. I heard
that array_agg() may have ORDER BY clause in its arguments but
currently not included in pgsql, whereas extract() is supported as
special case. I think we might build general rule for those and until
it is completed, RESPECT NULLS/IGNORENULLS and others can be ignored.
Of course it must be documented though.

Regards,


-- 
Hitoshi Harada