On Tuesday, May 3, 2016, drum.lucas@xxxxxxxxx <mailto:drum.lucas@xxxxxxxxx> <drum.lucas@xxxxxxxxx
<mailto:drum.lucas@xxxxxxxxx>> wrote:
* This is what I did...
|-- Creating the table
CREATE TABLE public.company_seqs
(company_id BIGINTNOT NULL,
last_seq BIGINTNOT NULL DEFAULT 1000,
CONSTRAINT company_seqs_pkPRIMARY KEY (company_id)
);
-- Creating the function
CREATE OR REPLACEFUNCTION 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
BEFOREINSERT
ON public.users
FOR EACHROW
EXECUTE PROCEDURE users_code_seq();|
1) I just may be over-sensitive to this, but after Adrian Klaver referred you to a ten-years old
post that the above looks an awful lot similar too, it sure would be nice to see some attribution
rather than claiming it as your own with "...what *I* did..."
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 <javascript:_e(%7B%7D,'cvml','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 <javascript:_e(%7B%7D,'cvml','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.|
2) Does the public.company_seqs have any rows in it?
3) Not sure you need a separate company_seq table. Since there is a one-to-one relation between
company and company_seqs, put the last_seq column in the company table.
-- Berend
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
