Re: Function PostgreSQL 9.2

On Tue, May 3, 2016 at 5:53 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 05/03/2016 02:27 PM, drum(dot)lucas(at)gmail(dot)com wrote:
>
>>
>>
>> On 4 May 2016 at 01:18, Melvin Davidson <melvin6925(at)gmail(dot)com
>> <mailto:melvin6925(at)gmail(dot)com>> wrote:
>>
>>
>>
>> On Tue, May 3, 2016 at 1:21 AM, David G. Johnston
>> <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>>
>> 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@ravnalaska.net
>
> in particular this link from the thread:
>
> http://www.postgresql.org/message-id/44E376F6.7010802@seaworthysys.com
>
>
>
>> 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(dot)klaver(at)aklaver(dot)com
>

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