XXXX.schema.actor.copy_inventory.sql

BEGIN;

-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);

CREATE TABLE asset.copy_inventory (
    id                          SERIAL                      PRIMARY KEY,
    inventory_workstation       INTEGER                     REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
    inventory_date              TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
    copy                        BIGINT                      NOT NULL
);
CREATE INDEX copy_inventory_copy_idx ON asset.copy_inventory (copy);
CREATE UNIQUE INDEX asset_copy_inventory_date_once_per_copy ON asset.copy_inventory (inventory_date, copy);

CREATE OR REPLACE FUNCTION evergreen.asset_copy_inventory_copy_inh_fkey() RETURNS TRIGGER AS $f$
BEGIN
        PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
        IF NOT FOUND THEN
                RAISE foreign_key_violation USING MESSAGE = FORMAT(
                        $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
                );
        END IF;
        RETURN NEW;
END;
$f$ LANGUAGE PLPGSQL VOLATILE COST 50;

CREATE CONSTRAINT TRIGGER inherit_asset_copy_inventory_copy_fkey
        AFTER UPDATE OR INSERT ON asset.copy_inventory
        DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_inventory_copy_inh_fkey();

INSERT INTO asset.copy_inventory
(inventory_workstation, inventory_date, copy)
SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy
FROM asset.latest_inventory
JOIN asset.copy acp ON acp.id = latest_inventory.copy
JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation
WHERE acp.circ_lib = workstation.owning_lib
UNION
SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy
FROM asset.latest_inventory
JOIN asset.copy acp ON acp.id = latest_inventory.copy
JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation
WHERE acp.circ_lib <> workstation.owning_lib
AND acp.floating IS NOT NULL;

DROP TABLE asset.latest_inventory;

CREATE VIEW asset.latest_inventory (id, inventory_workstation, inventory_date, copy) AS
SELECT DISTINCT ON (copy) id, inventory_workstation, inventory_date, copy
FROM asset.copy_inventory
ORDER BY copy, inventory_date DESC;

DROP FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey();

COMMIT;