Open-ILS/src/sql/Pg/upgrade/XXXX.function.hold-move-trigger-bug.sql

   1 BEGIN;
   2
   3 CREATE OR REPLACE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
   4 BEGIN
   5     IF TG_OP = 'INSERT' THEN
   6         INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
   7         SELECT  NEW.id,
   8                 NEW.target,
   9                 NEW.hold_type,
  10                 CASE
  11                     WHEN NEW.hold_type = 'T'
  12                         THEN NEW.target
  13                     WHEN NEW.hold_type = 'I'
  14                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
  15                     WHEN NEW.hold_type = 'V'
  16                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
  17                     WHEN NEW.hold_type IN ('C','R','F')
  18                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
  19                     WHEN NEW.hold_type = 'M'
  20                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
  21                     WHEN NEW.hold_type = 'P'
  22                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
  23                 END AS bib_record;
  24     ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
  25         UPDATE  reporter.hold_request_record
  26           SET   target = NEW.target,
  27                 hold_type = NEW.hold_type,
  28                 bib_record = CASE
  29                     WHEN NEW.hold_type = 'T'
  30                         THEN NEW.target
  31                     WHEN NEW.hold_type = 'I'
  32                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
  33                     WHEN NEW.hold_type = 'V'
  34                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
  35                     WHEN NEW.hold_type IN ('C','R','F')
  36                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
  37                     WHEN NEW.hold_type = 'M'
  38                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
  39                     WHEN NEW.hold_type = 'P'
  40                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
  41                 END
  42          WHERE  id = NEW.id;
  43     END IF;
  44     RETURN NEW;
  45 END;
  46 $$ LANGUAGE PLPGSQL;
  47
  48 COMMIT;
  49