Dyrcona

XXXX.schema.actor.copy_inventory.sql

Oct 20th, 2021
679
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. BEGIN;
  2.  
  3. -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
  4.  
  5. CREATE TABLE asset.copy_inventory (
  6.     id                          SERIAL                      PRIMARY KEY,
  7.     inventory_workstation       INTEGER                     REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
  8.     inventory_date              TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
  9.     copy                        BIGINT                      NOT NULL
  10. );
  11. CREATE INDEX copy_inventory_copy_idx ON asset.copy_inventory (copy);
  12. CREATE UNIQUE INDEX asset_copy_inventory_date_once_per_copy ON asset.copy_inventory (inventory_date, copy);
  13.  
  14. CREATE OR REPLACE FUNCTION evergreen.asset_copy_inventory_copy_inh_fkey() RETURNS TRIGGER AS $f$
  15. BEGIN
  16.         PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
  17.         IF NOT FOUND THEN
  18.                 RAISE foreign_key_violation USING MESSAGE = FORMAT(
  19.                         $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
  20.                 );
  21.         END IF;
  22.         RETURN NEW;
  23. END;
  24. $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
  25.  
  26. CREATE CONSTRAINT TRIGGER inherit_asset_copy_inventory_copy_fkey
  27.         AFTER UPDATE OR INSERT ON asset.copy_inventory
  28.         DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_inventory_copy_inh_fkey();
  29.  
  30. INSERT INTO asset.copy_inventory
  31. (inventory_workstation, inventory_date, copy)
  32. SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy
  33. FROM asset.latest_inventory
  34. JOIN asset.copy acp ON acp.id = latest_inventory.copy
  35. JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation
  36. WHERE acp.circ_lib = workstation.owning_lib
  37. UNION
  38. SELECT DISTINCT ON (inventory_date, copy) inventory_workstation, inventory_date, copy
  39. FROM asset.latest_inventory
  40. JOIN asset.copy acp ON acp.id = latest_inventory.copy
  41. JOIN actor.workstation ON workstation.id = latest_inventory.inventory_workstation
  42. WHERE acp.circ_lib <> workstation.owning_lib
  43. AND acp.floating IS NOT NULL;
  44.  
  45. DROP TABLE asset.latest_inventory;
  46.  
  47. CREATE VIEW asset.latest_inventory (id, inventory_workstation, inventory_date, copy) AS
  48. SELECT DISTINCT ON (copy) id, inventory_workstation, inventory_date, copy
  49. FROM asset.copy_inventory
  50. ORDER BY copy, inventory_date DESC;
  51.  
  52. DROP FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey();
  53.  
  54. COMMIT;
  55.  
RAW Paste Data