Re: Function PostgreSQL 9.2

>
>
> 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();