Re: Function PostgreSQL 9.2

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