Trigger function - PostgreSQL 9.2

Question

The idea:

1 - The customer can add any value into users.code column 2 - If the customer does not insert data into it, the trigger/funciton has to do the job

• I am able to do that, using this SQL:

 CREATE OR REPLACE FUNCTION tf_users_update_code_column()
 RETURNS trigger AS $$
 BEGIN
 IF NEW.company_id = 1 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c1_users_code_seq');
 ELSEIF NEW.company_id = 2 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c2_users_code_seq');
 ELSEIF NEW.company_id = 3 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c3_users_code_seq');
 ELSEIF NEW.company_id = 4 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c4_users_code_seq');
 ELSEIF NEW.company_id = 5 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c5_users_code_seq');
 ELSEIF NEW.company_id = 6 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c6_users_code_seq');
 ELSEIF NEW.company_id = 7 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c7_users_code_seq');
 ELSEIF NEW.company_id = 8 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c8_users_code_seq');
 ELSEIF NEW.company_id = 9 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c9_users_code_seq');
 ELSEIF NEW.company_id = 10 AND NEW.code IS NULL THEN
 NEW.code = NEXTVAL('c10_users_code_seq');
 END IF;
 return NEW;
 END
 $$ LANGUAGE plpgsql;
 2 - Creating the sequences....
 CREATE SEQUENCE c1_users_code_seq 
 INCREMENT 1 
 MINVALUE 1
 MAXVALUE 9223372036854775807 
 START 1000;
 CACHE 1;
 CREATE SEQUENCE c2_users_code_seq 
 INCREMENT 1 
 MINVALUE 1
 MAXVALUE 9223372036854775807 
 START 1000;
 CACHE 1;
 CREATE SEQUENCE c3_users_code_seq 
 INCREMENT 1 
 MINVALUE 1
 MAXVALUE 9223372036854775807 
 START 1000;
 CACHE 1;
 CREATE SEQUENCE c4_users_code_seq 
 INCREMENT 1 
 MINVALUE 1
 MAXVALUE 9223372036854775807 
 START 1000;
 CACHE 1;
 ... [etc] ...
 3 - Creating the TRIGGER
 CREATE TRIGGER t_users_update_code_column
 BEFORE INSERT
 ON users
 FOR EACH ROW
 EXECUTE PROCEDURE tf_users_update_code_column();

• But because there will be thousands of companies, I can't have thousands of sequences
• That's why I'm writing the code below:

 CREATE TABLE public.company_seqs
 (company_id BIGINT NOT NULL,
 last_seq BIGINT NOT NULL DEFAULT 0,
 CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)
 );
 CREATE OR REPLACE FUNCTION users_code_seq() RETURNS trigger AS $$
 DECLARE
 last_seq BIGINT;
 company_id BIGINT;
 code character varying;
 BEGIN
 IF (TG_OP = 'INSERT') THEN
 UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE company_id = NEW.company_id;
 ELSEIF NEW.code IS NULL THEN
 SELECT last_seq INTO NEW.code FROM public.company_seqs WHERE company_id = NEW.company_id;
 END IF;
 RETURN new;
 END;
 $$ LANGUAGE plpgsql;
 CREATE TRIGGER tf_users_code_seq
 BEFORE INSERT
 ON public.users
 FOR EACH ROW
 EXECUTE PROCEDURE users_code_seq();

When inserting data into the public.users table:

INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (671,'[email protected]','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01','default','1');
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (672,'[email protected]','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'1');
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (673,'[email protected]','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01',default,'1');
INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,code,company_id) VALUES (674,'[email protected]','bucefalo','0','2016-05-03 00:01:01','2016-05-03 00:01:01','something','1');

I got the following error:

BEGIN
psql:1.sql:3: ERROR: column reference "last_seq" is ambiguous
LINE 1: UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHE...
 ^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: UPDATE public.company_seqs SET last_seq = (last_seq + 1) WHERE company_id = NEW.company_id
CONTEXT: PL/pgSQL function users_code_seq() line 10 at SQL statement

What am I missing?

• user1822: If the column can't be null, then why don't you just define it as NOT NULL and a default value?
• user83914: @a_horse_with_no_name - I've updated the question with my findings and tests - I think it's more clear now. Please have a look

Answer 1

Final code: (It works)

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;
COMMIT TRANSACTION;
BEGIN;
-- Creating the function
CREATE OR REPLACE FUNCTION users_code_seq()
 RETURNS "trigger" AS $$
DECLARE code character varying;
BEGIN
-- if it's an insert, then we update the client_code_increment column value to +1
 IF (TG_OP = 'INSERT') THEN
 UPDATE public.companies SET client_code_increment = (client_code_increment + 1) WHERE id = NEW.company_id;
 END IF;
-- IF the customer didn't provide a code value, we insert the next available from companies.client_code_increment
 IF NEW.code IS NULL THEN
 SELECT client_code_increment INTO NEW.code FROM public.companies as c WHERE c.id = NEW.company_id ORDER BY client_code_increment DESC;
 END IF;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Creating the trigger
CREATE TRIGGER tf_users_code_seq
 BEFORE INSERT
 ON public.users
 FOR EACH ROW
 EXECUTE PROCEDURE users_code_seq();
COMMIT TRANSACTION;