Forum OpenACS Q&A: New documentation for tree_sortkey
It is available from here:
If you already know how to use tree_sortkey, please let me know if there are any errors in this document. It will be nice to be able to refer people to this in the future.
That is a great summary - I have always been a bit scared of an api that was emulating an Oracle feature I was scared of in the first place ;)
Your archive of documentation is truly prodigious!
Unfortunately LIKE doesn't utilize indexes in PostgreSQL if the installation's collation sequence isn't set to "C", as is true for folks using non-Latin character sets. So my implementation uses BIT VARYING which are treated like VARCHARs restricted to '0' and '1'. In Oracle, the Forums package implements tree_sortkeys using the RAW datatype.
You might want to reference that article in your doc.
And my name's spelled "Baccus" :)
The document has been updated, and also now includes an example that has a join to another table.
I believe you can't do that in Oracle, right? tree_sortkey seems like a really slick solution.
Very helpful reference Jade. Thanks for making this available. Great background document Don - thanks for this as well.
I've seen a few patches that claim to give PG the ability to perform Oracle like hierarchical queries. Does anyone have any knowledge on any of these?
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).
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.
I cannot open either link to the documentation. Can you send the documentation directly to me? This functionality will help resolve some coding issues for a new application.
Thanks for you help.