Re: Function PostgreSQL 9.2

drum(dot)lucas(at)gmail(dot)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,'test22(at)test(dot)com <mailto: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 <mailto: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;

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;