Open-ILS/src/sql/Pg/upgrade/XXXX-create-inventory-workstation-and-date.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
   4
   5 CREATE TABLE asset.last_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 last_copy_inventory_copy_idx ON asset.last_copy_inventory (copy);
  12
  13 CREATE OR REPLACE FUNCTION evergreen.asset_last_copy_inventory_copy_inh_fkey() RETURNS TRIGGER AS $f$
  14 BEGIN
  15         PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
  16         IF NOT FOUND THEN
  17                 RAISE foreign_key_violation USING MESSAGE = FORMAT(
  18                         $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
  19                 );
  20         END IF;
  21         RETURN NEW;
  22 END;
  23 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
  24
  25 CREATE CONSTRAINT TRIGGER inherit_asset_last_copy_inventory_copy_fkey
  26         AFTER UPDATE OR INSERT ON asset.last_copy_inventory
  27         DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_last_copy_inventory_copy_inh_fkey();
  28
  29 COMMIT;