1

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,'test1@test.com','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,'test2@test.com','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,'test3@test.com','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,'test4@test.com','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?

  • If the column can't be null, then why don't you just define it as NOT NULL and a default value? – a_horse_with_no_name May 1 '16 at 7:30
  • @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 – user83914 May 4 '16 at 1:50
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy