Re: base36 sequence

Tom Lane wrote:

>ninja <mail@schnake.freeserve.co.uk> writes:
>
>
>>I'm very new to pgsql but I was wondering if it is possible to use
>>CREATE SEQUENCE to make auto-increment base36 values for my ID column
>>[varchar(252)]. I don't see any base convert function, though.
>>Obviously, the reason I'm doing this is so as to maintain hierarchy. Is
>>it possible or will I have to increment the sequence outside of pgsql?
>>
>>
>
>If you don't mind my saying so, this seems like an exceedingly bizarre
>requirement.  Why don't you just use an integer column (or even more
>specifically, a serial column) for your ID?  Who could possibly care
>about a base36 representation for a serial number as opposed to a
>plain decimal representation?
>
>FWIW, you could surely write your own display-in-base36 function in
>plperl or pltcl.  I'm just mildly astonished that you consider this
>a significant issue.
>
>            regards, tom lane
>
>
>
>
Ah, on it's face it might seem completely inane (and maybe later, you'll
avail me of a better method) but allow me to explain.  I'm trying to
maintain hierarchy by appending IDs to the right of LINEAGE column.
This lineage can be a 252 character string, which allows for 63 levels
if the ids are 4 digits long.  I'm stopping at 252 because I believe
that 255 characters is the largest a string can be and remain
indexable.  When sorted by lineage, the hierarchy is returned.  It's not
an original idea.  If the ids were 9 digit integers that would only
allow 29 levels so the shorter the id the better really.