Re: Window Functions patch v06

2008/10/14 David Rowley <dgrowley(at)gmail(dot)com>:
> 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
>
>
>

Thanks for your feedback.

I agree I need to work on that. Also from the spec, "RESPECT NULLS /
IGNORE NULLS" may be specified but not supported yet. This syntax
specification is out of the postgres general function call so I wonder
if those functions are treated specially or not.

Regards,

--
Hitoshi Harada