CODEWITHSUNDEEP
mysqlpostgresql
munyso
munyso

Reputation: 87

Duplicate Auto Increment Generated Field in PostgreSQL Using Trigger

I have a problem about using trigger in PostgreSQL to create bar-code. The problem is that when Client Side access my web app and insert data in the same time, I need generate bar-code in some my purpose project. But the problem is that the Auto generated bar-code is Duplicate. Any solution, please help me. format bar-code is: 2016000000001. This is my trigger:

DROP TABLE IF EXISTS "test"."barcode";
CREATE TABLE "test"."barcode" (
"id" int8 DEFAULT nextval('"test".t_id_seq'::regclass) NOT NULL,
"barcode" varchar(255) COLLATE "default"
)
WITH (OIDS=FALSE);

-----------------------------------------

CREATE OR REPLACE FUNCTION test.my_trigger_function()
  RETURNS trigger AS
$BODY$DECLARE new_barcode CHAR(50);

BEGIN
    SELECT
    (CASE 
        WHEN 
            CHAR_LENGTH (CAST(CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1 AS CHAR(20))) = 1 THEN 
                (SUBSTRING(barcode, 1, 4) || '00000000' || CAST((CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1)  AS CHAR(20)))
        WHEN 
            CHAR_LENGTH (CAST(CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1 AS CHAR(20))) = 2 THEN 
                (SUBSTRING(barcode, 1, 4) || '0000000' || CAST((CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1)  AS CHAR(20)))
        WHEN 
            CHAR_LENGTH (CAST(CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1 AS CHAR(20))) = 3 THEN 
                (SUBSTRING(barcode, 1, 4) || '000000' || CAST((CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1)  AS CHAR(20)))
        WHEN 
            CHAR_LENGTH (CAST(CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1 AS CHAR(20))) = 4 THEN 
                (SUBSTRING(barcode, 1, 4) || '00000' || CAST((CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1)  AS CHAR(20)))
        WHEN 
            CHAR_LENGTH (CAST(CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1 AS CHAR(20))) = 5 THEN 
                (SUBSTRING(barcode, 1, 4) || '0000' || CAST((CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1)  AS CHAR(20)))
        WHEN 
            CHAR_LENGTH (CAST(CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1 AS CHAR(20))) = 6 THEN 
                (SUBSTRING(barcode, 1, 4) || '000' || CAST((CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1)  AS CHAR(20)))
        WHEN 
            CHAR_LENGTH (CAST(CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1 AS CHAR(20))) = 7 THEN 
                (SUBSTRING(barcode, 1, 4) || '00' || CAST((CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1)  AS CHAR(20)))
        WHEN 
            CHAR_LENGTH (CAST(CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1 AS CHAR(20))) = 8 THEN 
                (SUBSTRING(barcode, 1, 4) || '0' || CAST((CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1)  AS CHAR(20)))
        WHEN 
            CHAR_LENGTH (CAST(CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1 AS CHAR(20))) = 9 THEN 
                (SUBSTRING(barcode, 1, 4) || CAST((CAST(SUBSTRING(barcode, 5, 9) AS INTEGER) + 1)  AS CHAR(20)))
        ELSE
            (CAST((CAST(TO_CHAR(NOW(),'yyyy') AS INTEGER)+1) AS CHAR(20)) || '000000001')
    END
    ) INTO new_barcode
FROM
    test. barcode
ORDER BY
    id DESC
LIMIT 1;

IF new_barcode IS NULL THEN 
    new_barcode = (CAST(TO_CHAR(NOW(),'yyyy') AS CHAR(20)) || '000000001'); 
END IF;
    NEW.barcode = new_barcode;
    RETURN NEW;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION test.my_trigger_function()
  OWNER TO postgres;
--------------------------------------------------

CREATE TRIGGER "my_trigger" BEFORE INSERT ON "test"."barcode"
FOR EACH ROW
EXECUTE PROCEDURE "test"."my_trigger_function"();

Upvotes: 1

Views: 143

Answers (2)

Rémy Baron
Rémy Baron

Reputation: 1399

Try this :

create a sequence :

create sequence test.barcode_sequence_byyear  start 1;

and your trigger function :

CREATE OR REPLACE FUNCTION test.my_trigger_function()
RETURNS trigger AS
$BODY$DECLARE new_barcode CHAR(50);
  declare count_barcode_of_year integer;

BEGIN
 --If it is the first barcode of year then init sequence to 1 
  select count(*) into count_barcode_of_year from test.barcode where  barcode like to_char(now(),'YYYY')||'%';
  if count_barcode_of_year=0 then 
      --set sequence to 1
       PERFORM setval('test.barcode_sequence_byyear'::regclass,1,false);
  end if;

  --Format your new barcode with year and sequence ( YEAR + Sequence with 9 significatives zeros : 
    NEW.barcode=to_char(now(),'YYYY')||to_char(nextval('test.barcode_sequence_byyear'::regclass),'FM'||repeat('0',9));
   RETURN NEW;
END;$BODY$
 LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION test.my_trigger_function()
OWNER TO postgres;

Upvotes: 0

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

(My eyes bled...) This is what you can do instead, simply using a SEQUENCE (guaranteed not to incur in any duplicates):

CREATE SEQUENCE IF NOT EXISTS test.barcode_seq START 2016000000001;

CREATE OR REPLACE FUNCTION test.my_trigger_function() RETURNS trigger AS
$BODY$
DECLARE
  _barcode bigint;
BEGIN
  _barcode := nextval('test.barcode_seq');

  IF EXTRACT('year' FROM CURRENT_DATE) <> _barcode/1000000000 THEN
    _barcode := EXTRACT('year' FROM CURRENT_DATE) * 1000000000 + 1;
    PERFORM setval('test.barcode_seq', _barcode, true);
  END IF;

  NEW.barcode = _barcode::text;
  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions