Open-ILS/src/sql/Pg/version-upgrade/2.8.5-2.8.6-upgrade-db.sql

   1 --Upgrade Script for 2.8.5 to 2.8.6
   2 \set eg_version '''2.8.6'''
   3 BEGIN;
   4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.8.6', :eg_version);
   5
   6 SELECT evergreen.upgrade_deps_block_check('0950', :eg_version);
   7
   8 CREATE OR REPLACE FUNCTION money.materialized_summary_billing_del () RETURNS TRIGGER AS $$
   9 DECLARE
  10         prev_billing    money.billing%ROWTYPE;
  11         old_billing     money.billing%ROWTYPE;
  12 BEGIN
  13         SELECT * INTO prev_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1 OFFSET 1;
  14         SELECT * INTO old_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;
  15
  16         IF OLD.id = old_billing.id THEN
  17                 UPDATE  money.materialized_billable_xact_summary
  18                   SET   last_billing_ts = prev_billing.billing_ts,
  19                         last_billing_note = prev_billing.note,
  20                         last_billing_type = prev_billing.billing_type
  21                   WHERE id = OLD.xact;
  22         END IF;
  23
  24         IF NOT OLD.voided THEN
  25                 UPDATE  money.materialized_billable_xact_summary
  26                   SET   total_owed = total_owed - OLD.amount,
  27                         balance_owed = balance_owed - OLD.amount
  28                   WHERE id = OLD.xact;
  29         END IF;
  30
  31         RETURN OLD;
  32 END;
  33 $$ LANGUAGE PLPGSQL;
  34
  35 COMMIT;