1 -- Script to create the query schema and the tables therein
5 DROP SCHEMA IF EXISTS query CASCADE;
7 COMMENT ON SCHEMA actor IS $$
9 * Copyright (C) 2009 Equinox Software, Inc. / Georgia Public Library Service
10 * Scott McKellar <scott@esilibrary.com>
14 * Contains tables designed to represent user-defined queries for
15 * reports and the like.
19 * This program is free software; you can redistribute it and/or
20 * modify it under the terms of the GNU General Public License
21 * as published by the Free Software Foundation; either version 2
22 * of the License, or (at your option) any later version.
24 * This program is distributed in the hope that it will be useful,
25 * but WITHOUT ANY WARRANTY; without even the implied warranty of
26 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
27 * GNU General Public License for more details.
31 CREATE TABLE query.stored_query (
32 id SERIAL PRIMARY KEY,
33 type TEXT NOT NULL CONSTRAINT query_type CHECK
34 ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ),
35 use_all BOOLEAN NOT NULL DEFAULT FALSE,
36 use_distinct BOOLEAN NOT NULL DEFAULT FALSE,
37 from_clause INT, --REFERENCES query.from_clause
38 --DEFERRABLE INITIALLY DEFERRED,
39 where_clause INT, --REFERENCES query.expression
40 --DEFERRABLE INITIALLY DEFERRED,
41 having_clause INT --REFERENCES query.expression
42 --DEFERRABLE INITIALLY DEFERRED
45 -- (Foreign keys to be defined later after other tables are created)
47 CREATE TABLE query.query_sequence (
48 id SERIAL PRIMARY KEY,
49 parent_query INT NOT NULL
50 REFERENCES query.stored_query
52 DEFERRABLE INITIALLY DEFERRED,
54 child_query INT NOT NULL
55 REFERENCES query.stored_query
57 DEFERRABLE INITIALLY DEFERRED,
58 CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
61 CREATE TABLE query.datatype (
62 id SERIAL PRIMARY KEY,
63 datatype_name TEXT NOT NULL UNIQUE,
64 is_numeric BOOL NOT NULL DEFAULT FALSE,
65 is_composite BOOL NOT NULL DEFAULT FALSE,
66 CONSTRAINT qdt_comp_not_num CHECK
67 ( is_numeric IS FALSE OR is_composite IS FALSE )
70 CREATE TABLE query.subfield (
71 id SERIAL PRIMARY KEY,
72 composite_type INT NOT NULL
73 REFERENCES query.datatype(id)
75 DEFERRABLE INITIALLY DEFERRED,
77 CONSTRAINT qsf_pos_seq_no
79 subfield_type INT NOT NULL
80 REFERENCES query.datatype(id)
81 DEFERRABLE INITIALLY DEFERRED,
82 CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
85 CREATE TABLE query.function_sig (
86 id SERIAL PRIMARY KEY,
87 function_name TEXT NOT NULL,
88 return_type INT REFERENCES query.datatype(id)
89 DEFERRABLE INITIALLY DEFERRED,
90 is_aggregate BOOL NOT NULL DEFAULT FALSE,
91 CONSTRAINT qfd_rtn_or_aggr CHECK
92 ( return_type IS NULL OR is_aggregate = FALSE )
95 CREATE INDEX query_function_sig_name_idx
96 ON query.function_sig (function_name);
98 CREATE TABLE query.function_param_def (
99 id SERIAL PRIMARY KEY,
100 function_id INT NOT NULL
101 REFERENCES query.function_sig( id )
103 DEFERRABLE INITIALLY DEFERRED,
105 CONSTRAINT qfpd_pos_seq_no CHECK
107 datatype INT NOT NULL
108 REFERENCES query.datatype( id )
109 DEFERRABLE INITIALLY DEFERRED,
110 CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
113 CREATE TABLE query.bind_variable (
114 name TEXT PRIMARY KEY,
116 CONSTRAINT bind_variable_type CHECK
117 ( type in ( 'string', 'number', 'string_list', 'number_list' )),
118 description TEXT NOT NULL,
119 default_value TEXT, -- to be encoded in JSON
123 CREATE TABLE query.expression (
124 id SERIAL PRIMARY KEY,
125 type TEXT NOT NULL CONSTRAINT expression_type CHECK
128 'xbind', -- bind variable
137 'xisnull', -- is null
145 parenthesize BOOL NOT NULL DEFAULT FALSE,
146 parent_expr INT REFERENCES query.expression
148 DEFERRABLE INITIALLY DEFERRED,
149 seq_no INT NOT NULL DEFAULT 1,
153 left_operand INT REFERENCES query.expression
154 DEFERRABLE INITIALLY DEFERRED,
156 right_operand INT REFERENCES query.expression
157 DEFERRABLE INITIALLY DEFERRED,
158 function_id INT REFERENCES query.function_sig
159 DEFERRABLE INITIALLY DEFERRED,
160 subquery INT REFERENCES query.stored_query
161 DEFERRABLE INITIALLY DEFERRED,
162 cast_type INT REFERENCES query.datatype
163 DEFERRABLE INITIALLY DEFERRED,
164 negate BOOL NOT NULL DEFAULT FALSE,
165 bind_variable TEXT REFERENCES query.bind_variable
166 DEFERRABLE INITIALLY DEFERRED
169 CREATE UNIQUE INDEX query_expr_parent_seq
170 ON query.expression( parent_expr, seq_no )
171 WHERE parent_expr IS NOT NULL;
173 -- Due to some circular references, the following foreign key definitions
174 -- had to be deferred until query.expression existed:
176 ALTER TABLE query.stored_query
177 ADD FOREIGN KEY ( where_clause )
178 REFERENCES query.expression( id )
179 DEFERRABLE INITIALLY DEFERRED;
181 ALTER TABLE query.stored_query
182 ADD FOREIGN KEY ( having_clause )
183 REFERENCES query.expression( id )
184 DEFERRABLE INITIALLY DEFERRED;
186 CREATE TABLE query.case_branch (
187 id SERIAL PRIMARY KEY,
188 parent_expr INT NOT NULL REFERENCES query.expression
190 DEFERRABLE INITIALLY DEFERRED,
192 condition INT REFERENCES query.expression
193 DEFERRABLE INITIALLY DEFERRED,
194 result INT NOT NULL REFERENCES query.expression
195 DEFERRABLE INITIALLY DEFERRED,
196 CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
199 CREATE TABLE query.from_relation (
200 id SERIAL PRIMARY KEY,
201 type TEXT NOT NULL CONSTRAINT relation_type CHECK (
202 type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
205 subquery INT REFERENCES query.stored_query,
206 function_call INT REFERENCES query.expression,
208 parent_relation INT REFERENCES query.from_relation
210 DEFERRABLE INITIALLY DEFERRED,
211 seq_no INT NOT NULL DEFAULT 1,
212 join_type TEXT CONSTRAINT good_join_type CHECK (
213 join_type IS NULL OR join_type IN
214 ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
216 on_clause INT REFERENCES query.expression
217 DEFERRABLE INITIALLY DEFERRED,
218 CONSTRAINT join_or_core CHECK (
219 ( parent_relation IS NULL AND join_type IS NULL
220 AND on_clause IS NULL )
222 ( parent_relation IS NOT NULL AND join_type IS NOT NULL
223 AND on_clause IS NOT NULL )
227 CREATE UNIQUE INDEX from_parent_seq
228 ON query.from_relation( parent_relation, seq_no )
229 WHERE parent_relation IS NOT NULL;
231 -- The following foreign key had to be deferred until
232 -- query.from_relation existed
234 ALTER TABLE query.stored_query
235 ADD FOREIGN KEY (from_clause)
236 REFERENCES query.from_relation
237 DEFERRABLE INITIALLY DEFERRED;
239 CREATE TABLE query.record_column (
240 id SERIAL PRIMARY KEY,
241 from_relation INT NOT NULL REFERENCES query.from_relation
243 DEFERRABLE INITIALLY DEFERRED,
245 column_name TEXT NOT NULL,
246 column_type INT NOT NULL REFERENCES query.datatype
248 DEFERRABLE INITIALLY DEFERRED,
249 CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
252 CREATE TABLE query.select_item (
253 id SERIAL PRIMARY KEY,
254 stored_query INT NOT NULL REFERENCES query.stored_query
256 DEFERRABLE INITIALLY DEFERRED,
258 expression INT NOT NULL REFERENCES query.expression
259 DEFERRABLE INITIALLY DEFERRED,
261 grouped_by BOOL NOT NULL DEFAULT FALSE,
262 CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
265 CREATE TABLE query.order_by_item (
266 id SERIAL PRIMARY KEY,
267 stored_query INT NOT NULL REFERENCES query.stored_query
269 DEFERRABLE INITIALLY DEFERRED,
271 expression INT NOT NULL REFERENCES query.expression
273 DEFERRABLE INITIALLY DEFERRED,
274 CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
277 -- Create updatable views -------------------------------------------
279 -- Create updatable view for BETWEEN expressions
281 CREATE OR REPLACE VIEW query.expr_xbet AS
294 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
295 ON INSERT TO query.expr_xbet
297 INSERT INTO query.expression (
306 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
308 COALESCE(NEW.parenthesize, FALSE),
310 COALESCE(NEW.seq_no, 1),
312 COALESCE(NEW.negate, false)
315 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
316 ON UPDATE TO query.expr_xbet
318 UPDATE query.expression SET
320 parenthesize = NEW.parenthesize,
321 parent_expr = NEW.parent_expr,
323 left_operand = NEW.left_operand,
328 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
329 ON DELETE TO query.expr_xbet
331 DELETE FROM query.expression WHERE id = OLD.id;
333 -- Create updatable view for bind variable expressions
335 CREATE OR REPLACE VIEW query.expr_xbind AS
347 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
348 ON INSERT TO query.expr_xbind
350 INSERT INTO query.expression (
358 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
360 COALESCE(NEW.parenthesize, FALSE),
362 COALESCE(NEW.seq_no, 1),
366 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
367 ON UPDATE TO query.expr_xbind
369 UPDATE query.expression SET
371 parenthesize = NEW.parenthesize,
372 parent_expr = NEW.parent_expr,
374 bind_variable = NEW.bind_variable
378 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
379 ON DELETE TO query.expr_xbind
381 DELETE FROM query.expression WHERE id = OLD.id;
383 -- Create updatable view for boolean expressions
385 CREATE OR REPLACE VIEW query.expr_xbool AS
398 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
399 ON INSERT TO query.expr_xbool
401 INSERT INTO query.expression (
410 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
412 COALESCE(NEW.parenthesize, FALSE),
414 COALESCE(NEW.seq_no, 1),
416 COALESCE(NEW.negate, false)
419 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
420 ON UPDATE TO query.expr_xbool
422 UPDATE query.expression SET
424 parenthesize = NEW.parenthesize,
425 parent_expr = NEW.parent_expr,
427 literal = NEW.literal,
432 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
433 ON DELETE TO query.expr_xbool
435 DELETE FROM query.expression WHERE id = OLD.id;
437 -- Create updatable view for CASE expressions
439 CREATE OR REPLACE VIEW query.expr_xcase AS
452 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
453 ON INSERT TO query.expr_xcase
455 INSERT INTO query.expression (
464 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
466 COALESCE(NEW.parenthesize, FALSE),
468 COALESCE(NEW.seq_no, 1),
470 COALESCE(NEW.negate, false)
473 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
474 ON UPDATE TO query.expr_xcase
476 UPDATE query.expression SET
478 parenthesize = NEW.parenthesize,
479 parent_expr = NEW.parent_expr,
481 left_operand = NEW.left_operand,
486 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
487 ON DELETE TO query.expr_xcase
489 DELETE FROM query.expression WHERE id = OLD.id;
491 -- Create updatable view for cast expressions
493 CREATE OR REPLACE VIEW query.expr_xcast AS
507 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
508 ON INSERT TO query.expr_xcast
510 INSERT INTO query.expression (
520 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
522 COALESCE(NEW.parenthesize, FALSE),
524 COALESCE(NEW.seq_no, 1),
527 COALESCE(NEW.negate, false)
530 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
531 ON UPDATE TO query.expr_xcast
533 UPDATE query.expression SET
535 parenthesize = NEW.parenthesize,
536 parent_expr = NEW.parent_expr,
538 left_operand = NEW.left_operand,
539 cast_type = NEW.cast_type,
544 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
545 ON DELETE TO query.expr_xcast
547 DELETE FROM query.expression WHERE id = OLD.id;
549 -- Create updatable view for column expressions
551 CREATE OR REPLACE VIEW query.expr_xcol AS
565 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
566 ON INSERT TO query.expr_xcol
568 INSERT INTO query.expression (
578 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
580 COALESCE(NEW.parenthesize, FALSE),
582 COALESCE(NEW.seq_no, 1),
585 COALESCE(NEW.negate, false)
588 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
589 ON UPDATE TO query.expr_xcol
591 UPDATE query.expression SET
593 parenthesize = NEW.parenthesize,
594 parent_expr = NEW.parent_expr,
596 table_alias = NEW.table_alias,
597 column_name = NEW.column_name,
602 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
603 ON DELETE TO query.expr_xcol
605 DELETE FROM query.expression WHERE id = OLD.id;
607 -- Create updatable view for EXISTS expressions
609 CREATE OR REPLACE VIEW query.expr_xex AS
622 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
623 ON INSERT TO query.expr_xex
625 INSERT INTO query.expression (
634 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
636 COALESCE(NEW.parenthesize, FALSE),
638 COALESCE(NEW.seq_no, 1),
640 COALESCE(NEW.negate, false)
643 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
644 ON UPDATE TO query.expr_xex
646 UPDATE query.expression SET
648 parenthesize = NEW.parenthesize,
649 parent_expr = NEW.parent_expr,
651 subquery = NEW.subquery,
656 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
657 ON DELETE TO query.expr_xex
659 DELETE FROM query.expression WHERE id = OLD.id;
661 -- Create updatable view for field expressions
663 CREATE OR REPLACE VIEW query.expr_xfld AS
677 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
678 ON INSERT TO query.expr_xfld
680 INSERT INTO query.expression (
690 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
692 COALESCE(NEW.parenthesize, FALSE),
694 COALESCE(NEW.seq_no, 1),
697 COALESCE(NEW.negate, false)
700 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
701 ON UPDATE TO query.expr_xfld
703 UPDATE query.expression SET
705 parenthesize = NEW.parenthesize,
706 parent_expr = NEW.parent_expr,
708 column_name = NEW.column_name,
709 left_operand = NEW.left_operand,
714 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
715 ON DELETE TO query.expr_xfld
717 DELETE FROM query.expression WHERE id = OLD.id;
719 -- Create updatable view for function call expressions
721 CREATE OR REPLACE VIEW query.expr_xfunc AS
734 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
735 ON INSERT TO query.expr_xfunc
737 INSERT INTO query.expression (
746 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
748 COALESCE(NEW.parenthesize, FALSE),
750 COALESCE(NEW.seq_no, 1),
752 COALESCE(NEW.negate, false)
755 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
756 ON UPDATE TO query.expr_xfunc
758 UPDATE query.expression SET
760 parenthesize = NEW.parenthesize,
761 parent_expr = NEW.parent_expr,
763 function_id = NEW.function_id,
768 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
769 ON DELETE TO query.expr_xfunc
771 DELETE FROM query.expression WHERE id = OLD.id;
773 -- Create updatable view for IN expressions
775 CREATE OR REPLACE VIEW query.expr_xin AS
789 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
790 ON INSERT TO query.expr_xin
792 INSERT INTO query.expression (
802 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
804 COALESCE(NEW.parenthesize, FALSE),
806 COALESCE(NEW.seq_no, 1),
809 COALESCE(NEW.negate, false)
812 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
813 ON UPDATE TO query.expr_xin
815 UPDATE query.expression SET
817 parenthesize = NEW.parenthesize,
818 parent_expr = NEW.parent_expr,
820 left_operand = NEW.left_operand,
821 subquery = NEW.subquery,
826 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
827 ON DELETE TO query.expr_xin
829 DELETE FROM query.expression WHERE id = OLD.id;
831 -- Create updatable view for IS NULL expressions
833 CREATE OR REPLACE VIEW query.expr_xisnull AS
846 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
847 ON INSERT TO query.expr_xisnull
849 INSERT INTO query.expression (
858 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
860 COALESCE(NEW.parenthesize, FALSE),
862 COALESCE(NEW.seq_no, 1),
864 COALESCE(NEW.negate, false)
867 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
868 ON UPDATE TO query.expr_xisnull
870 UPDATE query.expression SET
872 parenthesize = NEW.parenthesize,
873 parent_expr = NEW.parent_expr,
875 left_operand = NEW.left_operand,
880 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
881 ON DELETE TO query.expr_xisnull
883 DELETE FROM query.expression WHERE id = OLD.id;
885 -- Create updatable view for NULL expressions
887 CREATE OR REPLACE VIEW query.expr_xnull AS
899 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
900 ON INSERT TO query.expr_xnull
902 INSERT INTO query.expression (
910 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
912 COALESCE(NEW.parenthesize, FALSE),
914 COALESCE(NEW.seq_no, 1),
915 COALESCE(NEW.negate, false)
918 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
919 ON UPDATE TO query.expr_xnull
921 UPDATE query.expression SET
923 parenthesize = NEW.parenthesize,
924 parent_expr = NEW.parent_expr,
930 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
931 ON DELETE TO query.expr_xnull
933 DELETE FROM query.expression WHERE id = OLD.id;
935 -- Create updatable view for numeric literal expressions
937 CREATE OR REPLACE VIEW query.expr_xnum AS
949 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
950 ON INSERT TO query.expr_xnum
952 INSERT INTO query.expression (
960 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
962 COALESCE(NEW.parenthesize, FALSE),
964 COALESCE(NEW.seq_no, 1),
968 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
969 ON UPDATE TO query.expr_xnum
971 UPDATE query.expression SET
973 parenthesize = NEW.parenthesize,
974 parent_expr = NEW.parent_expr,
976 literal = NEW.literal
980 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
981 ON DELETE TO query.expr_xnum
983 DELETE FROM query.expression WHERE id = OLD.id;
985 -- Create updatable view for operator expressions
987 CREATE OR REPLACE VIEW query.expr_xop AS
1002 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
1003 ON INSERT TO query.expr_xop
1005 INSERT INTO query.expression (
1016 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1018 COALESCE(NEW.parenthesize, FALSE),
1020 COALESCE(NEW.seq_no, 1),
1024 COALESCE(NEW.negate, false)
1027 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
1028 ON UPDATE TO query.expr_xop
1030 UPDATE query.expression SET
1032 parenthesize = NEW.parenthesize,
1033 parent_expr = NEW.parent_expr,
1034 seq_no = NEW.seq_no,
1035 left_operand = NEW.left_operand,
1036 operator = NEW.operator,
1037 right_operand = NEW.right_operand,
1042 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1043 ON DELETE TO query.expr_xop
1045 DELETE FROM query.expression WHERE id = OLD.id;
1047 -- Create updatable view for series expressions,
1048 -- i.e. series of expressions separated by operators
1050 CREATE OR REPLACE VIEW query.expr_xser AS
1063 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1064 ON INSERT TO query.expr_xser
1066 INSERT INTO query.expression (
1075 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1077 COALESCE(NEW.parenthesize, FALSE),
1079 COALESCE(NEW.seq_no, 1),
1081 COALESCE(NEW.negate, false)
1084 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1085 ON UPDATE TO query.expr_xser
1087 UPDATE query.expression SET
1089 parenthesize = NEW.parenthesize,
1090 parent_expr = NEW.parent_expr,
1091 seq_no = NEW.seq_no,
1092 operator = NEW.operator,
1097 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1098 ON DELETE TO query.expr_xser
1100 DELETE FROM query.expression WHERE id = OLD.id;
1102 -- Create updatable view for string literal expressions
1104 CREATE OR REPLACE VIEW query.expr_xstr AS
1116 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1117 ON INSERT TO query.expr_xstr
1119 INSERT INTO query.expression (
1127 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1129 COALESCE(NEW.parenthesize, FALSE),
1131 COALESCE(NEW.seq_no, 1),
1135 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1136 ON UPDATE TO query.expr_xstr
1138 UPDATE query.expression SET
1140 parenthesize = NEW.parenthesize,
1141 parent_expr = NEW.parent_expr,
1142 seq_no = NEW.seq_no,
1143 literal = NEW.literal
1147 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1148 ON DELETE TO query.expr_xstr
1150 DELETE FROM query.expression WHERE id = OLD.id;
1152 -- Create updatable view for subquery expressions
1154 CREATE OR REPLACE VIEW query.expr_xsubq AS
1167 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1168 ON INSERT TO query.expr_xsubq
1170 INSERT INTO query.expression (
1179 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1181 COALESCE(NEW.parenthesize, FALSE),
1183 COALESCE(NEW.seq_no, 1),
1185 COALESCE(NEW.negate, false)
1188 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1189 ON UPDATE TO query.expr_xsubq
1191 UPDATE query.expression SET
1193 parenthesize = NEW.parenthesize,
1194 parent_expr = NEW.parent_expr,
1195 seq_no = NEW.seq_no,
1196 subquery = NEW.subquery,
1201 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1202 ON DELETE TO query.expr_xsubq
1204 DELETE FROM query.expression WHERE id = OLD.id;