Re: Function PostgreSQL 9.2

On Tuesday, May 3, 2016, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com> wrote:

>
> - This is what I did...
>
> -- Creating the tableCREATE TABLE public.company_seqs(company_id BIGINT NOT NULL,
> last_seq BIGINT NOT NULL DEFAULT 1000,CONSTRAINT company_seqs_pk PRIMARY KEY (company_id));
>
> -- Creating the function
> CREATE OR REPLACE FUNCTION 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 triggerCREATE TRIGGER tf_users_code_seq
> BEFORE INSERT
> ON public.users
> FOR EACH ROW
> EXECUTE PROCEDURE users_code_seq();
>
>
> 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.
>
>
> This sounds like database pollution...

I'd suggest writing a psql script that can be executed against an empty
database and does everything you want it to do. Execute it against an
empty database. Capture the output to a file with echo all. Post the
script and the output.

Also, try "update returning"

I would also advise adding STRICT.

David J.