Обсуждение: serial arrays?

A recent message from a would-be mysql converter led me to realize
that we don't check for array decoration when we expand "serial".
So this is accepted but doesn't do what one might expect:

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?
        regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 21 Mar 2008 12:55:26 -0400
Tom Lane <tgl@sss.pgh.pa.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. Sane? None. We should throw an error. 

Sincerely,

Joshua D. Drake


- -- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH4+m0ATb/zqfZUUQRAhNgAKCR9dM4lw7Wu7p1OMHm7ESWi25gxwCgqUuL
RkTa6gZqUqUXTlM6h97nf30=
=bwi3
-----END PGP SIGNATURE-----

On Fri, Mar 21, 2008 at 10:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I don't see any sane explanation if we do provide that syntax!!!

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device

Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On Fri, 21 Mar 2008 12:55:26 -0400
> Tom Lane <tgl@sss.pgh.pa.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
INCREMENTELEMENT 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