Page Comparison

-- Change search path for the script
SET search_path = <tenant>_mod_inventory_storage;

CREATE OR REPLACE FUNCTION dewey_call_number(call_number text)
  RETURNS text AS $$
  DECLARE
    matches text[];
    class_digits text;
    class_decimal text;
    cutter text;
    other text;
  BEGIN
    matches = regexp_match(call_number, '^(\d+)(\.\d+)? *\.?(?:([A-Z]\d{1,3}(?:[A-Z]+)?) *(.*)|(.*))$');
    IF matches IS NULL THEN
      RETURN numerically_sortable(trim2null(call_number));
    END IF;
    class_digits = matches[1];
    class_decimal = matches[2];
    cutter = matches[3];
    other = numerically_sortable(trim2null(concat(trim2null(matches[4]), trim2null(matches[5]))));
    RETURN concat_ws(' ', concat(sortable_number(class_digits), class_decimal),
                          cutter,
                          other
                    );
  END;
$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT;

CREATE OR REPLACE FUNCTION lc_nlm_call_number(call_number text)
  RETURNS text AS $$
  DECLARE
    matches text[];
    classification text;
    classLetters text;
    classDigits text;
    classDecimal text;
    everythingElse text;
    classSuffix text;
    cutter text;
  BEGIN
    call_number = upper(call_number);
    matches = regexp_match(call_number, '^(([A-Z]+) *(?:(\d+)(\.\d+)?)?)(.*)$');
    IF matches IS NULL THEN
      RETURN trim(cutter_shelf_key(trim2null(call_number)));
    END IF;
    classification = trim(matches[1]);
    classLetters = trim(matches[2]);
    classDigits = trim(matches[3]);
    classDecimal = trim(matches[4]);
    everythingElse = matches[5];
    IF classDigits IS NULL THEN
      RETURN NULL;
    END IF;
    IF length(everythingElse) > 0 THEN
      -- combining greedy and non-greedy:
      -- https://www.postgresql.org/docs/current/functions-matching.html#POSIX-MATCHING-RULES
      matches = regexp_match(everythingElse, '(?:(.*?)(\.?[A-Z]\d+|^\.[A-Z]| \.[A-Z])(.*)){1,1}');
      IF matches IS NULL THEN
        classSuffix = trim2null(everythingElse);
      ELSE
        classSuffix = trim2null(matches[1]);
        cutter = trim(matches[2] || matches[3]);
      END IF;
    END IF;
    classSuffix = numerically_sortable(classSuffix);
    IF substr(classSuffix, 1, 1) BETWEEN 'A' AND 'Z' THEN
      classSuffix = '_' || classSuffix;
    END IF;
    cutter = cutter_shelf_key(cutter);
    return trim(concat_ws(' ', classLetters,
                               concat(length(classDigits), classDigits, classDecimal),
                               trim2null(classSuffix),
                               trim(cutter)
                         ));
  END;
$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT;

CREATE OR REPLACE FUNCTION su_doc_call_number(call_number text)
  RETURNS text AS $$
  DECLARE
    matches text[];
  BEGIN
    matches = regexp_match(upper(call_number),
        '^([A-Z]+)\s*(\d+)(\.(?:[A-Z]+\d*|\d+))(/(?:[A-Z]+(?:\d+(?:-\d+)?)?|\d+(?:-\d+)?))?:?(.*)$');
    IF matches IS NULL THEN
      RETURN su_doc_part(trim2null(upper(call_number)));
    END IF;
    RETURN concat_ws(' ', matches[1], su_doc_part(matches[2]), su_doc_part(matches[3]), su_doc_part(matches[4]), su_doc_part(matches[5]));
  END;
$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT;

CREATE OR REPLACE FUNCTION sortable_number(n text)
  RETURNS text AS $$
  DECLARE int_part text;
  BEGIN
    n = regexp_replace(n, '^0+', '');
    int_part = split_part(n, '.', 1);
    RETURN concat(length(int_part), n);
  END;
$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT;

CREATE OR REPLACE FUNCTION cutter_shelf_key(s text)
  RETURNS text AS $$
  DECLARE
    chunk text;
    matches text[];
    cutter text;
    suffix text;
    result text;
  BEGIN
    FOREACH chunk IN ARRAY regexp_split_to_array(s, '(?=\.?[A-Z][0-9])') LOOP
      matches = regexp_match(chunk, '([A-Z][0-9]+)(.*)');
      IF matches IS NULL THEN
        -- before the first cutter
        IF result IS NULL THEN
            result = trim2null(numerically_sortable(chunk));
        ELSE
            result = concat_ws(' ', result, trim2null(numerically_sortable(chunk)));
        END IF;
      ELSE
        cutter = matches[1];
        suffix = trim2null(numerically_sortable(matches[2]));
        result = concat_ws(' ', result, cutter, suffix);
      END IF;
    END LOOP;
    RETURN result;
  END;
$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT;