Open-ILS/src/sql/Pg/upgrade/1063.schema.inheritance-constraint-trigger.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('1063', :eg_version);
   4
   5 DO $temp$
   6 DECLARE
   7         r RECORD;
   8 BEGIN
   9
  10         FOR r IN SELECT t.table_schema AS sname,
  11                         t.table_name AS tname,
  12                         t.column_name AS colname,
  13                         t.constraint_name
  14                   FROM  information_schema.referential_constraints ref
  15                         JOIN information_schema.key_column_usage t USING (constraint_schema,constraint_name)
  16                   WHERE ref.unique_constraint_schema = 'asset'
  17                         AND ref.unique_constraint_name = 'copy_pkey'
  18         LOOP
  19
  20                 EXECUTE 'ALTER TABLE '||r.sname||'.'||r.tname||' DROP CONSTRAINT '||r.constraint_name||';';
  21
  22                 EXECUTE '
  23                         CREATE OR REPLACE FUNCTION evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey() RETURNS TRIGGER AS $f$
  24                         BEGIN
  25                                 PERFORM 1 FROM asset.copy WHERE id = NEW.'||r.colname||';
  26                                 IF NOT FOUND THEN
  27                                         RAISE foreign_key_violation USING MESSAGE = FORMAT(
  28                                                 $$Referenced asset.copy id not found, '||r.colname||':%s$$, NEW.'||r.colname||'
  29                                         );
  30                                 END IF;
  31                                 RETURN NEW;
  32                         END;
  33                         $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
  34                 ';
  35
  36                 EXECUTE '
  37                         CREATE CONSTRAINT TRIGGER inherit_'||r.constraint_name||'
  38                                 AFTER UPDATE OR INSERT OR DELETE ON '||r.sname||'.'||r.tname||'
  39                                 DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.'||r.sname||'_'||r.tname||'_'||r.colname||'_inh_fkey();
  40                 ';
  41         END LOOP;
  42 END
  43 $temp$;
  44
  45 COMMIT;
  46