Call Number DB functions

CREATE OR REPLACE FUNCTION mvlc_get_callnumber(
    cn_id bigint,
    target_lib integer)
  RETURNS integer AS
$BODY$
DECLARE
    existing_cn asset.call_number%ROWTYPE;
    new_cn BIGINT;
BEGIN
    SELECT INTO existing_cn * FROM asset.call_number WHERE id = cn_id;
    IF existing_cn.owning_lib = target_lib AND NOT existing_cn.deleted THEN
        RETURN existing_cn.id;
    ELSIF existing_cn.owning_lib = target_lib THEN
        SELECT INTO new_cn id FROM asset.call_number
            WHERE record = existing_cn.record
            AND owning_lib = target_lib
            AND label = existing_cn.label
            AND prefix = existing_cn.prefix
            AND suffix = existing_cn.suffix
            AND NOT deleted;
        IF FOUND THEN
            RETURN new_cn;
        ELSE
            UPDATE asset.call_number SET deleted = false WHERE id = existing_cn.id;
            RETURN existing_cn.id;
        END IF;
    ELSE
        SELECT INTO new_cn id FROM asset.call_number
            WHERE record = existing_cn.record
            AND owning_lib = target_lib
            AND label = existing_cn.label
            AND prefix = existing_cn.prefix
            AND suffix = existing_cn.suffix;
        IF FOUND THEN
            SELECT INTO new_cn evergreen.mvlc_get_callnumber(new_cn, target_lib); -- Let a recursive call handle "use or undelete"
        ELSE
            INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, prefix, suffix, label_class)
                VALUES (existing_cn.creator, existing_cn.editor, existing_cn.record, target_lib, existing_cn.label, existing_cn.prefix, existing_cn.suffix, existing_cn.label_class)
                RETURNING id INTO new_cn;
        END IF;
        RETURN new_cn;
    END IF;
    RAISE EXCEPTION 'Something went wrong with mvlc_get_callnumber: % %', cn_id, target_lib;
    RETURN -1; -- Bring back as pre-cat?
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION mvlc_relabel_callnumber(
    callnumber_id integer,
    new_label text)
  RETURNS integer AS
$BODY$
DECLARE
    callnumber asset.call_number%ROWTYPE;
    existing_cn asset.call_number%ROWTYPE;
BEGIN
    SELECT INTO callnumber *
    FROM asset.call_number WHERE id = callnumber_id AND NOT deleted;
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    IF callnumber.label = new_label THEN
        RETURN callnumber.id;
    END IF;
    SELECT INTO existing_cn *
    FROM asset.call_number
    WHERE label = new_label
    AND owning_lib = callnumber.owning_lib
    AND record = callnumber.record
    AND prefix = callnumber.prefix
    AND suffix = callnumber.suffix
    AND NOT deleted;
    IF FOUND THEN
        UPDATE asset.copy SET call_number = existing_cn.id
        WHERE call_number = callnumber.id AND NOT deleted;
        UPDATE action.hold_request SET target = existing_cn.id
        WHERE taget = callnumber.id AND hold_type = 'V';
        DELETE FROM asset.call_number WHERE id = callnumber.id;
        RETURN existing_cn.id;
    ELSE
        UPDATE asset.call_number SET label = new_label WHERE id = callnumber.id;
        RETURN callnumber.id;
    END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;