From: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Melvin Davidson <melvin6925(at)gmail(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Function PostgreSQL 9.2 |
Date: | 2016-05-04 04:01:29 |
Message-ID: | CAE_gQfUSOEjxwFeF_0TQcxLdJWnQ78scS7oj8M7CE=xOkJZe7g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
- 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','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','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.
From | Date | Subject | |
---|---|---|---|
Next Message | Guyren Howe | 2016-05-04 04:11:06 | Thoughts on "Love Your Database" |
Previous Message | Mike Sofen | 2016-05-04 02:06:02 | Re: Function PostgreSQL 9.2 |