drum.lu...@gmail.com wrote:
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 <mailto: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 <mailto: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;
I am pretty sure the above line is wrong. NEW.id refers to users.id, not the companies.id. Also, the
implementation presents a potential race condition, e.g., if two different sessions attempt an
insert almost simultaneously.
END IF;
IF (TG_OP = 'INSERT') THEN
UPDATE public.companies SET client_code_increment =
(client_code_increment + 1) WHERE
id = NEW.id;
Ditto w.r.t. NEW.id.
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
