Re: Autoincremental value

gnari wrote:

>From: <adburne@asocmedrosario.com.ar>:
>
>
>>Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now
>>I'm testing postgres.
>>In mysql there is a way to make a second autoincrement field, just:
>>
>>create table table1
>>(field1 integer,
>>field2 integer autoincrement,
>>primary key (field1,field2))
>>
>>when insert rows:
>>
>>insert into table1 (field1) values (1);
>>insert into table1 (field1) values (1);
>>insert into table1 (field1) values (2);
>>
>>and then select * from table1, you get:
>>field1| field2
>>------+-------
>>  1   |  1
>>  1   |  2
>>  2   |  1
>>------+-------
>>
>>there is a way to do this with postgres???
>>
>>
>
>it seems it is possible with triggers:
>
>create table table1 (
>  field1 integer,
>  field2 integer,
>  primary key (field1,field2)
>);
>
>create or replace function table1_subcnt()
>  returns trigger as '
>    begin
>      select coalesce(max(field2),0)+1 from table1
>        where field1=NEW.field1
>        into NEW.field2;
>      return NEW;
>    end;
>' language plpgsql;
>
>
>
...

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();


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.

HTH

BJ