Re: Function PostgreSQL 9.2

On Tue, May 3, 2016 at 2:27 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
wrote:

> On 4 May 2016 at 01:18, Melvin Davidson <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> 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 = 2 THEN
>> NEW.users_code = NEXTVAL(c2_users_code_seq);
>> ELSEIF 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?
>
>
​Sequences have some very nice concurrency properties built into them.
Using them is the least problematic solution though within the framework
you are proposing they do have some tradeoffs to consider.

I would say that a proposal to create a huge if/else block is not very
realistic.​ Sequences have names and you should be storing, passing
around, and using those names. You cannot avoid having thousands of names
and objects defined, and I have no clue how efficient sequence name lookup
is (probably reasonably so), but at least the rest of the logic should be
devoid of any huge conditional blocks like the above. If you think you
need such a block typically you want to encode said information into a
table instead.

You'll need the table regardless but if you want to avoid creating sequence
objects you'd have to write custom functions, similar to (subset of...)
those implemented for sequence manipulation, to talk query said table.

So, since sequence us just a record on a table, the only differences is it
is system table and not a user table, I would recommend simply using
sequences unless and until you can prove they are inadequate to your needs
AND can prove that whatever custom implementation you write is better.

The only other reasonable option is change your model and requirements to
something less complex.

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

​The purpose of this list is whatever people are willing to make of it -
within reason. I share the sentiment that your particular method of
education is becoming burdensome to the people who volunteer their time on
these lists to answer questions. As the alternative is to simply stop
replying to your emails be grateful that someone was at least willing to
tell you to try other avenues of education.

David J.