Re: Function PostgreSQL 9.2

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Tue, May 3, 2016 at 7:53 PM, drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> wrote:

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 

>The field "last_seq + 1" is ok, but how can I determine that the start point would be 1000?

Really, how hard is it to change 1 to 1000?

INSERT INTO company_seqs
   (company_id, last_seq  )

VALUES

   ( {whatever_new_company_id_id}, 1000};

Really, you need to start thinking for yourself, but first _learn database design_! That is why I recommended those books to you.

>not sure what v_seq_num is...

It is a variable in the TRIGGER FUNCTION, Again, you need to learn first.

Try reading the docs!
http://www.postgresql.org/docs/9.2/interactive/index.html
http://www.postgresql.org/docs/9.2/interactive/plpgsql.html

http://www.postgresqltutorial.com/postgresql-stored-procedures/
http://www.postgresqltutorial.com/creating-first-trigger-postgresql/

Use google search for additional information on PostgreSQL

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.