Re: Autoincremental value

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;

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