cwmars.delete_acnp.sql

-- Copyright © 2022 C/W MARS, Inc.
-- Author: Jason Stephenson <jstephenson@cwmars.org>
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.

CREATE SCHEMA IF NOT EXISTS cwmars;

CREATE TYPE cwmars.delete_acnp_result_t AS (
    id INTEGER,
    count INTEGER
);

CREATE FUNCTION cwmars.delete_acnp(acnp INTEGER, new_acnp INTEGER DEFAULT -1)
RETURNS cwmars.delete_acnp_result_t  AS
$$
DECLARE
    call_number_with_prefix asset.call_number%ROWTYPE;
    call_number_with_new_prefix asset.call_number%ROWTYPE;
    result cwmars.delete_acnp_result_t;
BEGIN

result.count := 0;
result.id := acnp;

FOR call_number_with_prefix IN
    SELECT *
    FROM asset.call_number
    WHERE prefix = acnp
LOOP
    BEGIN
        UPDATE asset.call_number
        SET prefix = new_acnp
        WHERE id = call_number_with_prefix.id;
        -- If this works with no exception, then all is well.
    EXCEPTION
        WHEN unique_violation THEN
            -- Find the corresponding call number that already exists
            -- with the new_acnp
            SELECT *
            FROM asset.call_number
            WHERE prefix = new_acnp
            AND label = call_number_with_prefix.label
            AND suffix = call_number_with_prefix.suffix
            AND record = call_number_with_prefix.record
            AND owning_lib = call_number_with_prefix.owning_lib
            AND deleted = FALSE
            INTO call_number_with_new_prefix;

            -- asset.*
            UPDATE asset.uri_call_number_map
            SET call_number = call_number_with_new_prefix.id
            WHERE call_number = call_number_with_prefix.id;

            UPDATE asset.copy
            SET call_number = call_number_with_new_prefix.id
            WHERE call_number = call_number_with_prefix.id;

            -- container.*
            UPDATE container.call_number_bucket_item
            SET target_call_number = call_number_with_new_prefix.id
            WHERE target_call_number = call_number_with_prefix.id;

            -- serial.*
            UPDATE serial.distribution
            SET bind_call_number = call_number_with_new_prefix.id
            WHERE bind_call_number = call_number_with_prefix.id;

            UPDATE serial.distribution
            SET receive_call_number = call_number_with_new_prefix.id
            WHERE receive_call_number = call_number_with_prefix.id;

            UPDATE serial.unit
            SET call_number = call_number_with_new_prefix.id
            WHERE call_number = call_number_with_prefix.id;

            -- The call number with the old prefix is not needed anymore
            UPDATE asset.call_number
            SET prefix = new_acnp, deleted = TRUE
            WHERE id = call_number_with_prefix.id;
    END;
    result.count := result.count + 1;
END LOOP;

-- Don't need the old acnp
DELETE FROM asset.call_number_prefix
WHERE id = acnp;

RETURN result;
END;
$$
LANGUAGE plpgsql;