First you should use a ltree type for the uri field :
Yes, it would be very good if I could use ltree rather than rolling my own, but ltree doesn't meet my use requirements.
- you write it foo.bar instead of /foo/bar
That is the problem: The point is to make look-up based on uri or filesystem path very efficient.
- there are operators on ltree types to express "is parent of", "is children of"
- these operators are indexed
I have written similar pure-SQL functions for my parent/uri system, though without the syntax shortcuts of the ltree operators.
[...]
Now about your update problem, when you rename foo.bar into foo.crum.bar you could, in a single update, replace all foo.bar by foo.crum.bar in all your table with the ltree operators and special functions.
That's an approach I hadn't considered. Hmmm, I'd have to read the ltree C sources to see how it does this....
And for your checks, you can add a CHECK on the url field to be sure it's equal to the url of the parent + the name of the current row. It'll make one more SELECT request, though.
Yes, I'd like to avoid extra selects as much as possible.
Thank you very much for your interaction -- very helpful. -- ________________ harrison@xxxxxxx
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
