From: | "drum(dot)lucas(at)gmail(dot)com" <drum(dot)lucas(at)gmail(dot)com> |
---|---|
To: | Berend Tober <btober(at)computer(dot)org> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, 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-05 06:42:21 |
Message-ID: | CAE_gQfX0AB_Y-hs7mBc1fguCTJxvWWOtuy2n6Rf61Uao8xt=CA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)test(dot)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(at)test(dot)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(at)test(dot)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(at)test(dot)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(at)test(dot)com','password','0','2016-05-03
> 00:01:01','2016-05-03 00:01:01',default,'2'); - *NO PASS*
Cheers
From | Date | Subject | |
---|---|---|---|
Next Message | Jacob Scott | 2016-05-05 06:52:47 | Possible causes for "tuple concurrently updated" error |
Previous Message | Szymon Lipiński | 2016-05-05 05:55:07 | Re: Thoughts on "Love Your Database" |