Thread: X.50x OID representation
I need to save something very similar to X.50x IOD:s, that is, paths where the path-components are numbers. For example: 10.5.5003.24.35. Futhermore, I need to sort these in numerical path order, so that if two paths are compared according to the first path component that differs, and this path component is compared numerically, so that e.g. 100 is considered greater than 1. Is there a suitable datatype in PostgreSQL, or some other way to achieve this? Storing the paths as strings would make path-components of different length compare wrongly, e.g. "91" would be considered greater than "900", since the second digit 1 is greater than the second digit 0. One possibility is to store the paths as strings but with the digits within each path component reversed, so that e.g. 4711 becomes 1174. This would work, given that the path-separator compares less than all digits, but is rather uggly and requires a bit of nasty rewriting when updating/inserting/reading rows. Any ideas? Thanks in advance, Egil
On Tue, Nov 07, 2006 at 03:57:43AM -0800, redhog wrote: > I need to save something very similar to X.50x IOD:s, that is, paths > where the path-components are numbers. For example: 10.5.5003.24.35. Looks like something for ltree, which is a datatype for storing tree-structured data. http://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib/ltree/README.ltree Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
> Looks like something for ltree, which is a datatype for storing > tree-structured data. > > http://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/contrib/ltree/README.ltree Unfourtunately, ltree seems to compare node labels in lexicographic order: "have their usual meanings. Comparison is doing in the order of direct tree traversing, children of a node are sorted lexicographic." Maybe it is possible to hack it to support numeric order, but that would mean I'd introduce a new non-approoved patch that users of my application would have to apply to their database in order to run my application. Since my application is vaguely security related, that is a big non-no - I need to keep dependencies and code simple and easy to get an overview of and review...