Gapless sequence example in PostgreSQL

gapless_sequence.sql

CREATE TABLE public.gapless_sequence (
  sequence_schema TEXT,
  sequence_name TEXT,
  last_value BIGINT DEFAULT 0,
  step INTEGER DEFAULT 1
);

ALTER TABLE public.gapless_sequence
  ADD CONSTRAINT gapless_sequence_pk PRIMARY KEY (sequence_schema, sequence_name);

ALTER TABLE public.gapless_sequence
  ADD CONSTRAINT manual_last_value_ck CHECK (last_value >= 0);

ALTER TABLE public.gapless_sequence
  ADD CONSTRAINT manual_step_ck CHECK (step > 0);

CREATE OR REPLACE FUNCTION public.create_gapless_sequence(sName TEXT, iLastValue BIGINT, iStep INTEGER) RETURNS void AS
$$
DECLARE
  sSequenceName TEXT;
  sSequenceSchema TEXT;
BEGIN
  sSequenceSchema := trim(split_part(sName, '.', 1));
  sSequenceName   := trim(split_part(sName, '.', 2));

  IF sSequenceName = '' THEN
    sSequenceName   := sSequenceSchema;
    sSequenceSchema := 'public';
  END IF;

  PERFORM *
    FROM public.gapless_sequence
   WHERE sequence_schema = sSequenceSchema
     AND sequence_name   = sSequenceName;

  IF FOUND THEN
    RAISE EXCEPTION E'Sequence \"%\" already exists, use \"drop_gapless_sequence\" function to drop it!', $1;
  END IF;

  INSERT INTO public.gapless_sequence(sequence_schema, sequence_name, last_value, step)
  VALUES(sSequenceSchema, sSequenceName, iLastValue, iStep);

  RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.create_gapless_sequence(sName TEXT, iLastValue BIGINT) RETURNS void AS
$$
BEGIN
  PERFORM public.create_gapless_sequence(sName, iLastValue, 1);
  RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.create_gapless_sequence(sName TEXT) RETURNS void AS
$$
BEGIN
  PERFORM public.create_gapless_sequence(sName, 0, 1);
  RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.drop_gapless_sequence(sName TEXT) RETURNS void AS
$$
DECLARE
  sSequenceName TEXT;
  sSequenceSchema TEXT;
BEGIN
  sSequenceSchema := trim(split_part(sName, '.', 1));
  sSequenceName   := trim(split_part(sName, '.', 2));

  IF sSequenceName = '' THEN
    sSequenceName   := sSequenceSchema;
    sSequenceSchema := 'public';
  END IF;

  DELETE
    FROM public.gapless_sequence
   WHERE sequence_schema = sSequenceSchema
     AND sequence_name   = sSequenceName;

  IF NOT FOUND THEN
    RAISE EXCEPTION E'Sequence \"%\" not exists, use \"create_gapless_sequence\" function to create it!', $1;
  END IF;

  RETURN;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.nextval_gapless_sequence(sName TEXT) RETURNS bigint AS
$$
DECLARE
  iNextVal BIGINT DEFAULT 0;
  iStep INTEGER DEFAULT 1;
  sSequenceName TEXT;
  sSequenceSchema TEXT;
BEGIN
  sSequenceSchema := trim(split_part(sName, '.', 1));
  sSequenceName   := trim(split_part(sName, '.', 2));

  IF sSequenceName = '' THEN
    sSequenceName   := sSequenceSchema;
    sSequenceSchema := 'public';
  END IF;

  SELECT last_value,
         step
    INTO iNextVal,
         iStep
    FROM public.gapless_sequence
   WHERE sequence_schema = sSequenceSchema
     AND sequence_name   = sSequenceName
     FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION E'Sequence \"%\" does not exists, use \"create_gapless_sequence\" function to create it!', $1;
  END IF;

  UPDATE public.gapless_sequence
     SET last_value = iNextVal + iStep
   WHERE sequence_schema = sSequenceSchema
     AND sequence_name   = sSequenceName;

  RETURN iNextVal + iStep;
END;
$$ LANGUAGE plpgsql;