Обсуждение: 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:
+1
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
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?
+1
If not, what behavior would be
sane?
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