> 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