Search Postgresql Archives

Re: Function PostgreSQL 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Tue, May 3, 2016 at 5:53 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 05/03/2016 02:27 PM, drum.lucas@xxxxxxxxx wrote:


On 4 May 2016 at 01:18, Melvin Davidson <melvin6925@xxxxxxxxx
<mailto:melvin6925@xxxxxxxxx>> wrote:



    On Tue, May 3, 2016 at 1:21 AM, David G. Johnston
    <david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>> wrote:


            Well.. I don't need to add a constraint if I already have a
            default value, that's right...


        Wrong
        David J.


    What you need is a TRIGGER function & TRIGGER that will select  and
    assign the next users_code based on company_id.
    I'm not going to write the whole thing for you, but here is part of
    the trigger function logic.

    eg: IF NEW.company_id = 1 THEN
                   NEW.users_code = NEXTVAL(c1_users_code_seq);
           ELSEIF NEW.company.id <http://NEW.company.id> = 2 THEN
                   NEW.users_code =  NEXTVAL(c2_users_code_seq);
           ELSEIF NEW.company.id <http://NEW.company.id> = 3 THEN
                   NEW.users_code =  NEXTVAL(c3_users_code_seq);
           ...
           ...
           ELSE
               <    something bad happened because NEW.company_id was
    not valid ?
           END IF;




Do I have to have one sequence peer company_id ? There will be
thousands.. isn't there a better way to do that?

A sequence just keeps on incrementing. If you want a gapless sequence for each company that each increment independently of each other then yes you will need a separate sequence for each.

For an alternate strategy see this related thread:

http://www.postgresql.org/message-id/2926B083-33C9-4648-8635-BC293C70ED45@xxxxxxxxxxxxxx

in particular this link from the thread:

http://www.postgresql.org/message-id/44E376F6.7010802@xxxxxxxxxxxxxxxx



    Seriously, get yourself the books I have recommended and study them
    BEFORE you continue attempting to design your database.
    You need a firm understanding of logical design & flow, otherwise
    you will be wasting your time.


That's what I'm doing.. Studying.. asking for some help to get a better
understand.... isn't this the purpose of this mail list?


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx



>Do I have to have one sequence peer company_id ? There will be thousands.. isn't there a better way to do that?

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.

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

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.
   
--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux