On 10/30/2015 09:53 AM, Jim Nasby wrote:
> On 10/29/15 5:29 PM, Eric Schwarzenbach wrote:
>> I'm just now converting that path to use a custom domain (along with
>> custom operators) instead of just being a string. (The custom operators
>> allow the paths to be sorted properly without each segment needing to be
>> filled with zeros to a fixed length.) (Also FWIW, the latest version of
>> this regexp is now '^([0-9]+.)*[0-9]+$')
>
> Have you looked at using int[]? It wouldn't be hard to go between that
> and the string representation using string_to_array() and
> array_to_string(). There's also a chance that eventually you'd be able
> to do FKs on it.
Do you mean making the column int[] and converting to string if needed,
or converting the string column to int[] for the purposes of the
ordering algorithm?
I did consider making the column int[] instead of a string, and it would
probably be slightly more efficient in a few ways. My main hesitations
were having to revisit the code that puts together this path, and
compatibility (at the moment we're only using PostgreSQL but we've had
to run on other databases for certain clients in the past, and in theory
are open to that in the future). I realize the compatibility concern is
a little humorous in light of having gone down the
custom-operator-for-sorting route, but I can always fall back to 0 padding.