Re: Autoincremental value

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;

create trigger table1_subcnt before insert on table1
  for each row execute procedure table1_subcnt();

insert into table1 (field1) values (1);
insert into table1 (field1) values (1);
insert into table1 (field1) values (2);

gnari=# select * from table1;
 field1 | field2
--------+-------
      1 |      1
      1 |      2
      2 |      1
(3 rows)


gnari