Add column "left_operand" to query.expr_xcase view.
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 0304.schema.query-expr-xcase-left-operand.sql
1 -- Add left_operand column to query.expr_xcase view
2
3 -- If the view doesn't exist, this drop will fail, and that's okay
4 DROP VIEW query.expr_xcase CASCADE;
5
6 BEGIN;
7
8 INSERT INTO config.upgrade_log (version) VALUES ('0304'); -- Scott McKellar
9
10 CREATE OR REPLACE VIEW query.expr_xcase AS
11     SELECT
12                 id,
13                 parenthesize,
14                 parent_expr,
15                 seq_no,
16                 left_operand,
17                 negate
18     FROM
19         query.expression
20     WHERE
21         type = 'xcase';
22
23 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
24     ON INSERT TO query.expr_xcase
25     DO INSTEAD
26     INSERT INTO query.expression (
27                 id,
28                 type,
29                 parenthesize,
30                 parent_expr,
31                 seq_no,
32                 left_operand,
33                 negate
34     ) VALUES (
35         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
36         'xcase',
37         COALESCE(NEW.parenthesize, FALSE),
38         NEW.parent_expr,
39         COALESCE(NEW.seq_no, 1),
40                 NEW.left_operand,
41                 COALESCE(NEW.negate, false)
42     );
43
44 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
45     ON UPDATE TO query.expr_xcase
46     DO INSTEAD
47     UPDATE query.expression SET
48         id = NEW.id,
49         parenthesize = NEW.parenthesize,
50         parent_expr = NEW.parent_expr,
51         seq_no = NEW.seq_no,
52                 left_operand = NEW.left_operand,
53                 negate = NEW.negate
54     WHERE
55         id = OLD.id;
56
57 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
58     ON DELETE TO query.expr_xcase
59     DO INSTEAD
60     DELETE FROM query.expression WHERE id = OLD.id;
61
62 COMMIT;