Re: serial arrays?

Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Fri, 21 Mar 2008 12:55:26 -0400
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> regression=# create table foo (f1 serial[11]);
>> NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for
>> serial column "foo.f1" CREATE TABLE
>> regression=# \d foo
>> Table "public.foo"
>> Column | Type | Modifiers
>> --------+---------+--------------------------------------------------
>> f1 | integer | not null default nextval('foo_f1_seq'::regclass)
>>
>>
>> Should we throw an error for this? If not, what behavior would be
>> sane?
>
> Interesting? Would be to create 11 sequences that can update each
> element of the array.

Would you increment one element at a time? The first element in the
first nextval, the second element in the next... or would it increment
the first till it was 10 then the second till it was 10.... Or would you
increment each element by one for each nextval so each element is the
same number (use same sequence)?

I would think the most elegant solution would be to create an
array_sequence type. Which would open a great multitude of rule
definitions on how to define how each element is incremented. Well
probably a simple syntax that can end up with a complex list of rules
saved for the sequence that could be hard to decipher later or by the
next dba to come along.

As much as I can see at least one use for this (think number plate
sequences - 0-36 for each element) and some curiosity as a challenging
project, I do think this would be better handled by functions designed
specifically for the app that wants them.

Hmmmm, It could be an intriguing feature, but I'm not sure it would get
much use.

CREATE SEQUENCE_ARRAY my_silly_seq AS
integer[11] ON INCREMENT APPLY FROM ELEMENT 0,
ELEMENT 0 FROM 0 TO 36 ON LIMIT INCREMENT ELEMENT 1 AND RESET TO 0,
ELEMENT 1 FROM 0 TO 9 ON LIMIT INCREMENT ELEMENT 2 AND RESET TO 0,
...

Could there be char[] array that can increment from 0-9 then a-z before
rolling back to 0?

Guess I got too much time on my hands... I'll go find something better
to do now. ;-)

> Sane? None. We should throw an error.

+1 for the error

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz