Forum OpenACS Q&A: Re: New documentation for tree_sortkey

Collapse
Posted by Roberto Mello on
There's a patch that gives PG the Oracle CONNECT BY statement. That patch was refused by the PG core team because Oracle's CONNECT BY is:

1) Non-standard
2) Not as functional as the SQL 99 statements for hierarchical queries.

Or as Tom Lane would put it "Non-standard and broken."

So the PG team will implement the SQL 99 syntax. The Red Hat DB team (which Tom Lane belongs to) will do it, I think for 7.5 (don't think it made it to 7.4).

-Roberto

Collapse
Posted by Randy Ferrer on

I thought as much. I've never been a fan of CONNECT BY which so many people seem so enamoured with. Apart from the fact that it is rather limited in its use, it also violates relational closure. This is a problem since there is a high probability of information loss as you query further - just the behaviour you need when working with trees right? One of my professors at school was very keen on demonstrating the problems and it can get very messy.

There is an excellent chapter in "Practical Issues in Database Management" by F. Pascal if anyone is interested in more details. There are other issues as well, but suffice it to say that Connect by is far from an ideal solution. I couldn't imagine anything attempting to clone it's behaviour any better. Thanks for the info Roberto.