Re: Window Functions patch v06

Hitoshi Harada wrote:
>I made up my mind to scratch former window functions and redesigned
>completely new execution model, based on the discussion with Heikki.
>Attached is the v06 against HEAD today.
>http://umitanuki.net/pgsql/wfv06/design.html

First off, fantastic work!

In my eyes this and WITH RECURSIVE are a big step for both Postgres and open
source RBDMS'.

Only, one small query with LEAD() and LAG()

Going by http://www.wiscorp.com/sql200n.zip

"The lead and lag functions each take three arguments, a <value expression>
VE, an <exact numeric literal>
OFFSET, and a <value expression> DEFAULT. For each row R within the window
partition P of R defined by
a window structure descriptor, the lag function returns the value of VE
evaluated on a row that is OFFSET
number of rows before R within P, and the lead function returns the value of
VE evaluated on a row that is
OFFSET number of rows after R within P. The value of DEFAULT is returned as
the result if there is no row
corresponding to the OFFSET number of rows before R within P (for the lag
function) or after R within P (for
the lead function). In addition, RESPECT NULLS or IGNORE NULLS can be
specified to indicate whether
the rows within P for which VE evaluates to the null value are preserved or
eliminated"

So going by that:
SELECT name,LAG(name,1,'None') OVER (ORDER BY employeeid) FROM employee;

Would use 'None' for rows that would be out of the bounds of the window.

The current patch only seems to accept 2 arguments.
ERROR: function lag(character varying, integer, unknown) does not exist