> > > > I agree that having thousands of sequences can be hard to manage, > especially in a function, but you did not state that fact before, > only that you wanted separate sequences for each company. That > being said, here is an alternate solution. >
Yep.. that was my mistake. > > 1. CREATE TABLE company_seqs > (company_id bigint NOT NULL, > last_seq bigint NOT NULL, > CONSTRAINT company_seqs_pk PRIMARY KEY (company_id) > ); > > 2. Every time you create a new company, you must insert the > corresponding company_id and last_seq [which will be 1} > into the company_seqs table. > ok that's right.. just a comment here... the value inside the users.code column must start with 1000 and not 1. So, it would be 1001, 1002, 1003, etc. The field "last_seq + 1" is ok, but how can I determine that the start point would be 1000? > > 3. Change the trigger function logic to something like below: > > DECLARE > v_seq_num INTEGER; > > BEGIN > SELECT last_seq > FROM company_seqs > WHERE company_id = NEW.company_id INTO v_seq_num; > UPDATE company_seqs > SET last_seq = last_seq + 1 > WHERE company_id = NEW.company_id; > > new.users_code = v_seq_num; > not sure what v_seq_num is... > > > Now, just a quick comment. As has been said before, wanting a sequence > with no gaps for > each user in each company is a bit unrealistic and serves no purpose. For > example, > company_id 3 has 10 users, 1 > 10. What do you do when user 3 leaves and > is deleted? > As long as you have a unique user_code for each user, it does not matter. > > >... Studying.. asking for some help to get a better understand.... isn't > this the purpose of this mail list? > > Yes, but at the same time, it is evident that you are trying to design the > database before you have > a valid understanding of database design. To wit, you are putting the cart > before the horse. > While this list is here to help you, it is not meant as a DATABASE 101 > course. > Yep.. got it
