Re: Autoincremental value

gnari wrote:

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

>
>
>