Re: Function PostgreSQL 9.2 - Mailing list pgsql-general

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