Re: Window Functions patch v06 - Mailing list pgsql-hackers

From Hitoshi Harada
Subject Re: Window Functions patch v06
Date
Msg-id e08cc0400810131748t766f46b2u64f505654d586a8b@mail.gmail.com
Whole thread Raw
In response to Re: Window Functions patch v06  ("David Rowley" <dgrowley@gmail.com>)
Responses Re: Window Functions patch v06
List pgsql-hackers
2008/10/14 David Rowley <dgrowley@gmail.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


pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: WITH RECURSIVE ... CYCLE in vanilla SQL: issues with arrays of rows
Next
From: Tom Lane
Date:
Subject: Re: Window Functions patch v06