By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,863 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

trigger problem

P: 1
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
Oct 28 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 700
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();
Oct 30 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.