Open-ILS/src/sql/Pg/upgrade/0298.schema.xbet-left-operand.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0298'); -- Scott McKellar
   4
   5 -- Create updatable view for BETWEEN expressions
   6
   7 DROP VIEW query.expr_xbet CASCADE;
   8
   9 CREATE OR REPLACE VIEW query.expr_xbet AS
  10     SELECT
  11                 id,
  12                 parenthesize,
  13                 parent_expr,
  14                 seq_no,
  15                 left_operand,
  16                 negate
  17     FROM
  18         query.expression
  19     WHERE
  20         type = 'xbet';
  21
  22 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
  23     ON INSERT TO query.expr_xbet
  24     DO INSTEAD
  25     INSERT INTO query.expression (
  26                 id,
  27                 type,
  28                 parenthesize,
  29                 parent_expr,
  30                 seq_no,
  31                 left_operand,
  32                 negate
  33     ) VALUES (
  34         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
  35         'xbet',
  36         COALESCE(NEW.parenthesize, FALSE),
  37         NEW.parent_expr,
  38         COALESCE(NEW.seq_no, 1),
  39                 NEW.left_operand,
  40                 COALESCE(NEW.negate, false)
  41     );
  42
  43 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
  44     ON UPDATE TO query.expr_xbet
  45     DO INSTEAD
  46     UPDATE query.expression SET
  47         id = NEW.id,
  48         parenthesize = NEW.parenthesize,
  49         parent_expr = NEW.parent_expr,
  50         seq_no = NEW.seq_no,
  51                 left_operand = NEW.left_operand,
  52                 negate = NEW.negate
  53     WHERE
  54         id = OLD.id;
  55
  56 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
  57     ON DELETE TO query.expr_xbet
  58     DO INSTEAD
  59     DELETE FROM query.expression WHERE id = OLD.id;
  60
  61 COMMIT;