2008/10/14 David Rowley <[EMAIL PROTECTED]>: > 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
