Search Postgresql Archives

Re: Function PostgreSQL 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux