Re: Autoincremental value

gnari wrote:

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