I'm just having some problem when doing: INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,company_id) > VALUES (66,'tes...@test.com','password','0','2016-05-03 > 00:01:01','2016-05-03 00:01:01','15');
- see that I'm not providing the "code" column value? If I run the query above, I get the following error: > ERROR: query returned no rows > CONTEXT: PL/pgSQL function users_code_seq() line 7 at SQL statement - If I include the code column with a default value: > INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) > VALUES (4,'te...@test.com','password','0','2016-05-03 > 00:01:01','2016-05-03 00:01:01',default,'2'); I get the same error - Please, if anyone can help with that.. I'd appreciate it. *The final function code is:* CREATE OR REPLACE FUNCTION users_code_seq() > RETURNS "trigger" AS $$ > DECLARE code character varying; > BEGIN > IF NEW.code IS NULL THEN > SELECT client_code_increment INTO STRICT NEW.code FROM > public.companies WHERE id = NEW.id ORDER BY client_code_increment DESC; > END IF; > IF (TG_OP = 'INSERT') THEN > UPDATE public.companies SET client_code_increment = > (client_code_increment + 1) WHERE id = NEW.id; > END IF; > RETURN NEW; > END; > $$ LANGUAGE plpgsql; *companies.client_code_increment:* ALTER TABLE public.companies ADD COLUMN client_code_increment integer; > ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT > NULL; > ALTER TABLE public.companies ALTER COLUMN client_code_increment SET > DEFAULT 1000; *Trigger:* > CREATE TRIGGER tf_users_code_seq > BEFORE INSERT > ON public.users > FOR EACH ROW > EXECUTE PROCEDURE users_code_seq(); *Tests I'm doing:* *1 - Insert data into companies table:* > INSERT INTO > public.companies(id,name,created_at,updated_at,client_code_increment) > VALUES (1,'Company 1','2016-05-03 00:01:01','2016-05-03 00:01:01',default); > - *PASS* > INSERT INTO > public.companies(id,name,created_at,updated_at,client_code_increment) > VALUES (2,'Company 2','2016-05-03 00:01:01','2016-05-03 > 00:01:01',default); *- PASS* *2 - insert data into users table:* > INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) > VALUES (1,'te...@test.com','password','0','2016-05-03 > 00:01:01','2016-05-03 00:01:01','default','2'); - > *PASS*INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) > VALUES (2,'te...@test.com','password','0','2016-05-03 > 00:01:01','2016-05-03 00:01:01',default,'1'); - > *NO PASS*INSERT INTO public.users > (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) > VALUES (3,'te...@test.com','password','0','2016-05-03 > 00:01:01','2016-05-03 00:01:01',default,'2'); - *NO PASS* Cheers
