Re: Function PostgreSQL 9.2

["drum.lucas@xxxxxxxxx" <drum.lucas@xxxxxxxxx>]

• This is what I did...

-- Creating the table
CREATE 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 trigger
CREATE 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@xxxxxxxx','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@xxxxxxxx','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.