Re: Autoincremental value

"Brendan Jurd" <blakjak(at)blakjak(dot)sytes(dot)net> wrote:

>
> gnari wrote:
>
> >From: <adburne(at)asocmedrosario(dot)com(dot)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