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

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('1004', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
   6 BEGIN
   7     IF TG_OP = 'INSERT' THEN
   8         INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
   9         SELECT  NEW.id,
  10                 NEW.target,
  11                 NEW.hold_type,
  12                 CASE
  13                     WHEN NEW.hold_type = 'T'
  14                         THEN NEW.target
  15                     WHEN NEW.hold_type = 'I'
  16                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
  17                     WHEN NEW.hold_type = 'V'
  18                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
  19                     WHEN NEW.hold_type IN ('C','R','F')
  20                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
  21                     WHEN NEW.hold_type = 'M'
  22                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
  23                     WHEN NEW.hold_type = 'P'
  24                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
  25                 END AS bib_record;
  26     ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
  27         UPDATE  reporter.hold_request_record
  28           SET   target = NEW.target,
  29                 hold_type = NEW.hold_type,
  30                 bib_record = CASE
  31                     WHEN NEW.hold_type = 'T'
  32                         THEN NEW.target
  33                     WHEN NEW.hold_type = 'I'
  34                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
  35                     WHEN NEW.hold_type = 'V'
  36                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
  37                     WHEN NEW.hold_type IN ('C','R','F')
  38                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
  39                     WHEN NEW.hold_type = 'M'
  40                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
  41                     WHEN NEW.hold_type = 'P'
  42                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
  43                 END
  44          WHERE  id = NEW.id;
  45     END IF;
  46     RETURN NEW;
  47 END;
  48 $$ LANGUAGE PLPGSQL;
  49
  50 TRUNCATE TABLE reporter.hold_request_record;
  51
  52 INSERT INTO reporter.hold_request_record
  53 SELECT  id,
  54         target,
  55         hold_type,
  56         CASE
  57                 WHEN hold_type = 'T'
  58                         THEN target
  59                 WHEN hold_type = 'I'
  60                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target)
  61                 WHEN hold_type = 'V'
  62                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
  63                 WHEN hold_type IN ('C','R','F')
  64                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
  65                 WHEN hold_type = 'M'
  66                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
  67                 WHEN hold_type = 'P'
  68                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target)
  69         END AS bib_record
  70   FROM  action.hold_request ahr;
  71
  72 REINDEX TABLE reporter.hold_request_record;
  73
  74 COMMIT;
  75
  76 ANALYZE reporter.hold_request_record;
  77