Tom Lane Wrote: > I've spent quite a bit of time reviewing the window functions patch, > and I think it is now ready to commit, other than the documentation > (which I've not looked at yet at all). Attached is my current patch > against HEAD, sans documentation. This incorporates the recently > discussed aggregate-function API changes and support for tuplestore > trimming. There's a number of things that could be improved yet: > * we really ought to have some support for non-built-in > window functions > * I think the planner could be a bit smarter about when to > sort or not > * tuplestore_advance and related code really needs to be made > more efficient; it didn't matter much before but it does now > but I think these things can be worked on after the core patch is > committed. > > regards, tom lane
I've started running my test queries that I used when reviewing the patch. The following crashes the backend: CREATE TABLE billofmaterials ( parentpart VARCHAR(20) NOT NULL, childpart VARCHAR(20) NOT NULL, quantity FLOAT NOT NULL, CHECK(quantity > 0), PRIMARY KEY(parentpart, childpart) ); INSERT INTO billofmaterials VALUES('KITCHEN','TABLE',1); INSERT INTO billofmaterials VALUES('KITCHEN','COOKER',1); INSERT INTO billofmaterials VALUES('KITCHEN','FRIDGE',1); INSERT INTO billofmaterials VALUES('TABLE','CHAIR',4); INSERT INTO billofmaterials VALUES('CHAIR','LEG',4); WITH RECURSIVE bom AS ( SELECT parentpart,childpart,quantity,ROW_NUMBER() OVER (ORDER BY parentpart DESC) rn FROM billofmaterials WHERE parentpart = 'KITCHEN' UNION ALL SELECT b.parentpart,b.childpart,b.quantity,ROW_NUMBER() OVER (ORDER BY parentpart ASC) rn FROM billofmaterials b INNER JOIN bom ON b.parentpart = bom.childpart ) SELECT * from bom; It seems not to like recursively calling row_number(). It does not crash if I replace the 2nd row_number() with the constant 1 I compared everything to Oracle again and found no differences in results. These tests test all window functions in some way or another. I compared all results to Oracle 10g results apart from the queries that have NTH_VALUE as this is not implemented by Oracle 10g. Also seems like NTH_VALUE is not implemented by DB2 9.5 either. Anyone know of any database that does have NTH_VALUE? David. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers