Re: Function PostgreSQL 9.2 - Mailing list pgsql-general
| From | drum.lucas@gmail.com |
|---|---|
| Subject | Re: Function PostgreSQL 9.2 |
| Date | |
| Msg-id | CAE_gQfX0AB_Y-hs7mBc1fguCTJxvWWOtuy2n6Rf61Uao8xt=CA@mail.gmail.com Whole thread Raw |
| In response to | Re: Function PostgreSQL 9.2 ("drum.lucas@gmail.com" <drum.lucas@gmail.com>) |
| Responses |
Re: Function PostgreSQL 9.2
Re: Function PostgreSQL 9.2 |
| List | pgsql-general |
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,'test22@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,'test4@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,'test1@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,'test2@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,'test3@test.com','password','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'2'); - NO PASS
Cheers
pgsql-general by date: