trigger problem

[greno]

Is there any way to have a table with fields that would auto increment based on some data value? What I want is a table that has an 'id' field that would be auto increment. And then a field for a 'type' and then a field for the auto increment for this type. Here illustrates with some values:

id type typeid
1 1 1
2 2 1
3 2 2
4 3 1
5 1 2
6 2 3

Can you see the sequences? The table keeps a master sequence and each type has it's own sequence.

I know that a trigger will be necessary to do this. But I haven't thought of a good way to write it.

Gerry

[rski]

Is there any way to have a table with fields that would auto increment based on some data value? What I want is a table that has an 'id' field that would be auto increment. And then a field for a 'type' and then a field for the auto increment for this type. Here illustrates with some values:

id type typeid
1 1 1
2 2 1
3 2 2
4 3 1
5 1 2
6 2 3

Can you see the sequences? The table keeps a master sequence and each type has it's own sequence.

I know that a trigger will be necessary to do this. But I haven't thought of a good way to write it.

Gerry

Here is your trigger function, I think it works (but I think it can be done in simpler way)

create or replace function do_auto() returns trigger as $b$
declare
seq RECORD;
count int;
begin
count:=0;
for seq in select * from tablename where type=NEW.type loop
count:=count+1;
end loop;
NEW.typeid:=count+1;
return NEW;
end;
$b$ language plpgsql;

and trigger definition

create trigger tablename_tr before insert on tablename fro each row execute procedure do_auto();