Re: Function PostgreSQL 9.2

drum.lucas@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,'test22@test.com <mailto: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 <mailto: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;



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;