Re: Function PostgreSQL 9.2

["drum.lucas@xxxxxxxxx" <drum.lucas@xxxxxxxxx>]

1) You attached users_code_seq() to a trigger on the users table.

yes

 

2) You have a where clause:  company_id = NEW.id

3) NEW refers to users

4) NEW.id is obstensibly a USER ID

No...

CREATE OR REPLACE FUNCTION users_code_seq()
   RETURNS "trigger" AS $$
DECLARE code character varying;
BEGIN
        IF (TG_OP = 'INSERT') THEN
        UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.id;
        END IF;
        IF NEW.code IS NULL THEN
        SELECT client_code_increment INTO NEW.code FROM public.companies as c WHERE c.id = NEW.id ORDER BY client_code_increment DESC;
        END IF;
        RETURN NEW;
END;
$$ LANGUAGE plpgsql;

 

I'm updating the companies table... The company table has a column called ID.

If I change that to company_id, I get the error:  column "company_id" does not exist, because that column is inside USERS and not COMPANIES.

So as far as I can see here, that command is right.

- If I'm wrong, please, explain to me. 

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

Just to be clear here:

1 - 

ALTER TABLE public.companies ADD COLUMN client_code_increment integer;
ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT NULL;
ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAULT 1000;

2 - 

CREATE OR REPLACE FUNCTION users_code_seq()
   RETURNS "trigger" AS $$
DECLARE code character varying;
BEGIN
        IF (TG_OP = 'INSERT') THEN
        UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE company_id = NEW.id;
        END IF;
        IF NEW.code IS NULL THEN
        SELECT client_code_increment INTO NEW.code FROM public.companies as c WHERE c.id = NEW.id ORDER BY client_code_increment DESC;
        END IF;
        RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3 - 

CREATE TRIGGER tf_users_code_seq
   BEFORE INSERT
   ON public.users
   FOR EACH ROW
   EXECUTE PROCEDURE users_code_seq();