lp1777675 inventory date support
[working/Evergreen.git] / 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;