Re: Function PostgreSQL 9.2

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Tuesday, May 3, 2016, drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> wrote:

• 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.

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.