Re: Function PostgreSQL 9.2

> On Tuesday, May 3, 2016, drum.lucas@gmail.com <mailto:drum.lucas@gmail.com> <drum.lucas@gmail.com
> <mailto:drum.lucas@gmail.com>> wrote:
>
>       * This is what I did...
>
>         |-- Creating the table
>         CREATE  TABLE  public.company_seqs
>         (company_id BIGINTNOT  NULL,
>         last_seq BIGINTNOT  NULL  DEFAULT  1000,
>         CONSTRAINT  company_seqs_pkPRIMARY  KEY  (company_id)
>         );
>
>
>         -- Creating the function
>
>         CREATE  OR  REPLACEFUNCTION  users_code_seq()
>             RETURNS"trigger"  AS
>         '
>         BEGIN
>                  UPDATE public.company_seqs
>                          SET last_seq = (last_seq + 1)
>                          WHERE company_id = NEW.company_id;
>                  SELECT INTO NEW.code last_seq
>                          FROM public.company_seqs WHERE company_id = NEW.company_id;
>                  END IF;
>                  RETURN new;
>         END
>         '
>
>                  LANGUAGE'plpgsql'  VOLATILE;
>
>         -- Creating the trigger
>         CREATE  TRIGGER  tf_users_code_seq
>             BEFOREINSERT
>             ON  public.users
>             FOR  EACHROW
>             EXECUTE  PROCEDURE  users_code_seq();|



1) I just may be over-sensitive to this, but after Adrian Klaver referred you to a ten-years old
post that the above looks an awful lot similar too, it sure would be nice to see some attribution
rather than claiming it as your own with "...what *I* did..."

>
>
>         When inserting data:
>
>     |INSERT  INTO  public.users(id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id)
VALUES (672,'test2@test.com  <javascript:_e(%7B%7D,'cvml','test2@test.com');>','bucefalo','0','2016-05-03
00:01:01','2016-05-0300:01:01',default,'1'); 
>
>     INSERT  INTO  public.users(id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id)
VALUES (672,'test3@test.com  <javascript:_e(%7B%7D,'cvml','test3@test.com');>','bucefalo','0','2016-05-03
00:01:01','2016-05-0300:01:01','inserting my own data code column','1');| 
>
>       *
>
>         On the first query, nothing happens on the users.code column. The column is null.
>
>       *
>
>         On the second query, I can see the "inserting my own data code column" inserted into the
>         code column. |This means my Trigger function is not working.. I don't know why.|



2) Does the public.company_seqs have any rows in it?


3) Not sure you need a separate company_seq table. Since there is a one-to-one relation between
company and company_seqs, put the last_seq column in the company table.


-- Berend