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

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