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.
I don't think so..
Even because if I change that to company_id, I get the error:
ERROR: column "company_id" does not exist
END IF; IF (TG_OP = 'INSERT') THEN UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.id;