Re: Function PostgreSQL 9.2

> On Tuesday, May 3, 2016, drum(dot)lucas(at)gmail(dot)com <mailto:drum(dot)lucas(at)gmail(dot)com> <drum(dot)lucas(at)gmail(dot)com
> <mailto:drum(dot)lucas(at)gmail(dot)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(at)test(dot)com <javascript:_e(%7B%7D,'cvml','test2(at)test(dot)com');>','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00: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(at)test(dot)com <javascript:_e(%7B%7D,'cvml','test3(at)test(dot)com');>','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00: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