Re: Function PostgreSQL 9.2

On 05/03/2016 02:27 PM, drum.lucas@gmail.com wrote:
>
>
> On 4 May 2016 at 01:18, Melvin Davidson <melvin6925@gmail.com
> <mailto:melvin6925@gmail.com>> wrote:
>
>
>
>     On Tue, May 3, 2016 at 1:21 AM, David G. Johnston
>     <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.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.klaver@aklaver.com