Re: Autoincremental value
От | gnari |
---|---|
Тема | Re: Autoincremental value |
Дата | |
Msg-id | 001501c481e1$24f61bf0$0100000a@wp2000 обсуждение исходный текст |
Ответ на | Autoincremental value (adburne@asocmedrosario.com.ar) |
Ответы |
Re: Autoincremental value
|
Список | pgsql-general |
"Brendan Jurd" <blakjak@blakjak.sytes.net> wrote: > > gnari wrote: > > >From: <adburne@asocmedrosario.com.ar>: > > > > > >>[mysql implementation of autoincrement as second field in primary key] > >> ... > >>and then select * from table1, you get: > >>field1| field2 > >>------+------- > >> 1 | 1 > >> 1 | 2 > >> 2 | 1 > >>------+------- > > > >[trigger implementation using max] > > ... > > Rather than using an aggregate function ( max() ) on the table, which > could be expensive over a very great number of rows, why not use a > sequence? If it's good enough for a true serial, then it should be good > enough for this value-dependant one. You'd still use the trigger, but > simplify it. Like so: > > CREATE SEQUENCE table1_field2_seq; > > CREATE OR REPLACE FUNCTION fill_field2() RETURNS trigger AS ' > BEGIN > IF new.field2 IS NULL THEN > SELECT nextval( ''table1_field2_seq'' ) INTO new.field2 > END IF; > RETURN new; > END; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER insert_table1 BEFORE INSERT on table1 FOR EACH ROW > EXECUTE PROCEDURE fill_field2(); > how is this any different than a regular serial ? > > This gives the same result, without the added burden of running MAX for > every insert, and because it's a sequence, the results will work even if > multiple inserts are trying to run at very similar times. I agree that the use of MAX is weak, but the point was that the OP wanted the mysql behaviour. gnari
В списке pgsql-general по дате отправления: