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

From David Rowley
Subject Re: Window Functions patch v06
Date
Msg-id 9571E61244484B6F8A622002647516C9@amd64
Whole thread Raw
In response to Re: Window Functions patch v06  ("Hitoshi Harada" <umi.tanuki@gmail.com>)
Responses Re: Window Functions patch v06
List pgsql-hackers
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




pgsql-hackers by date:

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