Re: Function PostgreSQL 9.2

>
>
>
> 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