I wrote: > I was also reading over the standard tonight. I've discovered that the > OFFSET in LEAD() and LAG() is optional. It should default to 1 if it is > not present. Oracle seems to support this. > > SQL2008 says: > > If <lead or lag function> is specified, then: > > i) Let VE1 be <lead or lag extent> and let DT be the declared type of > VE1. > > ii) Case: > > Scalar expressions 209 > > WD 9075-2:200w(E) > > 6.10 <window function> > > If <offset> is specified, then let OFF be <offset>. The declared type of > >OFF shall be an > > exact numeric type with scale 0 (zero). > > 1) > > 2) Otherwise, let OFF be 1 (one). > > Yet another variant of LEAD() and LAG() but I think well worth it for both > compliance to the standard and compatibility to Oracle.
I figured this was quite simple so I've created a patch to implement this. Can probably put this down to the fact that I'm starting to feel bad about pointing out the mistakes and having someone else fix them. Figured it was time to make some changes myself. I've got limited experience with diff so please let me know if there is something wrong with the patch. Same goes for my changes to the code. I re-sequenced the OIDs of other window functions so it will require initdb. Also I made some updates to the documentation. Wasn't 100% sure on the syntax for the optional arguments there. Hitoshi had: arg1 [,optional1]. I've changed this to arg, [optional1], [optional2]. One thing I didn't do was update the regression test: SELECT oid, proname FROM pg_proc WHERE proiswfunc; Hopefully this patch will apply after applying Heikki's latest patch (version 3). If you're happy with this Heikki can you merge to your patch? David
windowfunc_nooffset1.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
