Re: Function PostgreSQL 9.2

On Tue, May 3, 2016 at 5:06 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
> On Tue, May 3, 2016 at 7:53 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com
> > 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
>
>
​+1

And ​I'll add to the doc list. Make sure to follow links around and not
just read the linked pages.​

​http://www.postgresql.org/docs/9.5/interactive/sql-altersequence.html
​
​David J.​