Re: Autoincremental value

gnari wrote:

>"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 ?
>
>
>
It isn't.  My post was a marvellous example of why I shouldn't try to
solve pgsql problems first thing after waking up, and *especially* why I
shouldn't post my solutions without testing them out first.

Don't know what I was thinking.

>>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

>
>
>