On Thu, May 5, 2016 at 1:22 PM, drum.lu...@gmail.com <drum.lu...@gmail.com> wrote:
> > > On 6 May 2016 at 02:29, David G. Johnston <david.g.johns...@gmail.com> > wrote: > >> On Thu, May 5, 2016 at 3:54 AM, Alban Hertroys <haram...@gmail.com> >> wrote: >> >>> >>> > On 05 May 2016, at 8:42, drum.lu...@gmail.com wrote: >>> >>> > 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; >>> >>> >>> ^^^^^^^ >>> There's your problem. I'm pretty sure the keyword STRICT isn't valid >>> there. It probably gets interpreted as a column name. >>> >>> >> No, its a sanity check/assertion. If that trips its because there is no >> company having a value of NEW.id on the public.companies table. If that is >> OK then remove the STRICT but if you are indeed expecting a record to be >> present and it is not it is correctly telling you that there is a problem >> in the data. Namely that said company needs to be added to the table. >> >> David J. >> >> > > > Taking off the "STRICT", the errors were gone. But still, it's not > working. Please have a look below. > So, the error messages are gone - the underlying error still exists. > If I use the other table: > > CREATE TABLE public.company_seqs >> (company_id BIGINT NOT NULL, >> last_seq BIGINT NOT NULL DEFAULT 1000, >> CONSTRAINT company_seqs_pk PRIMARY KEY (company_id) >> ); > > > It works fine.. the problem is when I try to use the companies table.. > which is already there and I just add another column > named: client_code_increment > > haven't found the problem yet... > You make this hard to help without a fully self-contained example for people to read. Berend already identified the problem for you. 1) You attached users_code_seq() to a trigger on the users table. 2) You have a where clause: company_id = NEW.id 3) NEW refers to users 4) NEW.id is obstensibly a USER ID 5) So you are basically saying: WHERE company_id = user_id 6) If you were to get match it would be entirely by accident - say because you used the same integer for both id values Hope that helps. David J.
