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
451 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
452 ON INSERT TO query.expr_xcase
454 INSERT INTO query.expression (
462 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
464 COALESCE(NEW.parenthesize, FALSE),
466 COALESCE(NEW.seq_no, 1),
467 COALESCE(NEW.negate, false)
470 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
471 ON UPDATE TO query.expr_xcase
473 UPDATE query.expression SET
475 parenthesize = NEW.parenthesize,
476 parent_expr = NEW.parent_expr,
482 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
483 ON DELETE TO query.expr_xcase
485 DELETE FROM query.expression WHERE id = OLD.id;
487 -- Create updatable view for cast expressions
489 CREATE OR REPLACE VIEW query.expr_xcast AS
503 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
504 ON INSERT TO query.expr_xcast
506 INSERT INTO query.expression (
516 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
518 COALESCE(NEW.parenthesize, FALSE),
520 COALESCE(NEW.seq_no, 1),
523 COALESCE(NEW.negate, false)
526 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
527 ON UPDATE TO query.expr_xcast
529 UPDATE query.expression SET
531 parenthesize = NEW.parenthesize,
532 parent_expr = NEW.parent_expr,
534 left_operand = NEW.left_operand,
535 cast_type = NEW.cast_type,
540 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
541 ON DELETE TO query.expr_xcast
543 DELETE FROM query.expression WHERE id = OLD.id;
545 -- Create updatable view for column expressions
547 CREATE OR REPLACE VIEW query.expr_xcol AS
561 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
562 ON INSERT TO query.expr_xcol
564 INSERT INTO query.expression (
574 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
576 COALESCE(NEW.parenthesize, FALSE),
578 COALESCE(NEW.seq_no, 1),
581 COALESCE(NEW.negate, false)
584 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
585 ON UPDATE TO query.expr_xcol
587 UPDATE query.expression SET
589 parenthesize = NEW.parenthesize,
590 parent_expr = NEW.parent_expr,
592 table_alias = NEW.table_alias,
593 column_name = NEW.column_name,
598 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
599 ON DELETE TO query.expr_xcol
601 DELETE FROM query.expression WHERE id = OLD.id;
603 -- Create updatable view for EXISTS expressions
605 CREATE OR REPLACE VIEW query.expr_xex AS
618 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
619 ON INSERT TO query.expr_xex
621 INSERT INTO query.expression (
630 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
632 COALESCE(NEW.parenthesize, FALSE),
634 COALESCE(NEW.seq_no, 1),
636 COALESCE(NEW.negate, false)
639 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
640 ON UPDATE TO query.expr_xex
642 UPDATE query.expression SET
644 parenthesize = NEW.parenthesize,
645 parent_expr = NEW.parent_expr,
647 subquery = NEW.subquery,
652 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
653 ON DELETE TO query.expr_xex
655 DELETE FROM query.expression WHERE id = OLD.id;
657 -- Create updatable view for field expressions
659 CREATE OR REPLACE VIEW query.expr_xfld AS
673 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
674 ON INSERT TO query.expr_xfld
676 INSERT INTO query.expression (
686 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
688 COALESCE(NEW.parenthesize, FALSE),
690 COALESCE(NEW.seq_no, 1),
693 COALESCE(NEW.negate, false)
696 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
697 ON UPDATE TO query.expr_xfld
699 UPDATE query.expression SET
701 parenthesize = NEW.parenthesize,
702 parent_expr = NEW.parent_expr,
704 column_name = NEW.column_name,
705 left_operand = NEW.left_operand,
710 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
711 ON DELETE TO query.expr_xfld
713 DELETE FROM query.expression WHERE id = OLD.id;
715 -- Create updatable view for function call expressions
717 CREATE OR REPLACE VIEW query.expr_xfunc AS
730 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
731 ON INSERT TO query.expr_xfunc
733 INSERT INTO query.expression (
742 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
744 COALESCE(NEW.parenthesize, FALSE),
746 COALESCE(NEW.seq_no, 1),
748 COALESCE(NEW.negate, false)
751 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
752 ON UPDATE TO query.expr_xfunc
754 UPDATE query.expression SET
756 parenthesize = NEW.parenthesize,
757 parent_expr = NEW.parent_expr,
759 function_id = NEW.function_id,
764 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
765 ON DELETE TO query.expr_xfunc
767 DELETE FROM query.expression WHERE id = OLD.id;
769 -- Create updatable view for IN expressions
771 CREATE OR REPLACE VIEW query.expr_xin AS
785 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
786 ON INSERT TO query.expr_xin
788 INSERT INTO query.expression (
798 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
800 COALESCE(NEW.parenthesize, FALSE),
802 COALESCE(NEW.seq_no, 1),
805 COALESCE(NEW.negate, false)
808 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
809 ON UPDATE TO query.expr_xin
811 UPDATE query.expression SET
813 parenthesize = NEW.parenthesize,
814 parent_expr = NEW.parent_expr,
816 left_operand = NEW.left_operand,
817 subquery = NEW.subquery,
822 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
823 ON DELETE TO query.expr_xin
825 DELETE FROM query.expression WHERE id = OLD.id;
827 -- Create updatable view for IS NULL expressions
829 CREATE OR REPLACE VIEW query.expr_xisnull AS
842 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
843 ON INSERT TO query.expr_xisnull
845 INSERT INTO query.expression (
854 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
856 COALESCE(NEW.parenthesize, FALSE),
858 COALESCE(NEW.seq_no, 1),
860 COALESCE(NEW.negate, false)
863 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
864 ON UPDATE TO query.expr_xisnull
866 UPDATE query.expression SET
868 parenthesize = NEW.parenthesize,
869 parent_expr = NEW.parent_expr,
871 left_operand = NEW.left_operand,
876 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
877 ON DELETE TO query.expr_xisnull
879 DELETE FROM query.expression WHERE id = OLD.id;
881 -- Create updatable view for NULL expressions
883 CREATE OR REPLACE VIEW query.expr_xnull AS
895 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
896 ON INSERT TO query.expr_xnull
898 INSERT INTO query.expression (
906 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
908 COALESCE(NEW.parenthesize, FALSE),
910 COALESCE(NEW.seq_no, 1),
911 COALESCE(NEW.negate, false)
914 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
915 ON UPDATE TO query.expr_xnull
917 UPDATE query.expression SET
919 parenthesize = NEW.parenthesize,
920 parent_expr = NEW.parent_expr,
926 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
927 ON DELETE TO query.expr_xnull
929 DELETE FROM query.expression WHERE id = OLD.id;
931 -- Create updatable view for numeric literal expressions
933 CREATE OR REPLACE VIEW query.expr_xnum AS
945 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
946 ON INSERT TO query.expr_xnum
948 INSERT INTO query.expression (
956 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
958 COALESCE(NEW.parenthesize, FALSE),
960 COALESCE(NEW.seq_no, 1),
964 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
965 ON UPDATE TO query.expr_xnum
967 UPDATE query.expression SET
969 parenthesize = NEW.parenthesize,
970 parent_expr = NEW.parent_expr,
972 literal = NEW.literal
976 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
977 ON DELETE TO query.expr_xnum
979 DELETE FROM query.expression WHERE id = OLD.id;
981 -- Create updatable view for operator expressions
983 CREATE OR REPLACE VIEW query.expr_xop AS
998 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
999 ON INSERT TO query.expr_xop
1001 INSERT INTO query.expression (
1012 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1014 COALESCE(NEW.parenthesize, FALSE),
1016 COALESCE(NEW.seq_no, 1),
1020 COALESCE(NEW.negate, false)
1023 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
1024 ON UPDATE TO query.expr_xop
1026 UPDATE query.expression SET
1028 parenthesize = NEW.parenthesize,
1029 parent_expr = NEW.parent_expr,
1030 seq_no = NEW.seq_no,
1031 left_operand = NEW.left_operand,
1032 operator = NEW.operator,
1033 right_operand = NEW.right_operand,
1038 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1039 ON DELETE TO query.expr_xop
1041 DELETE FROM query.expression WHERE id = OLD.id;
1043 -- Create updatable view for series expressions,
1044 -- i.e. series of expressions separated by operators
1046 CREATE OR REPLACE VIEW query.expr_xser AS
1059 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1060 ON INSERT TO query.expr_xser
1062 INSERT INTO query.expression (
1071 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1073 COALESCE(NEW.parenthesize, FALSE),
1075 COALESCE(NEW.seq_no, 1),
1077 COALESCE(NEW.negate, false)
1080 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1081 ON UPDATE TO query.expr_xser
1083 UPDATE query.expression SET
1085 parenthesize = NEW.parenthesize,
1086 parent_expr = NEW.parent_expr,
1087 seq_no = NEW.seq_no,
1088 operator = NEW.operator,
1093 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1094 ON DELETE TO query.expr_xser
1096 DELETE FROM query.expression WHERE id = OLD.id;
1098 -- Create updatable view for string literal expressions
1100 CREATE OR REPLACE VIEW query.expr_xstr AS
1112 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1113 ON INSERT TO query.expr_xstr
1115 INSERT INTO query.expression (
1123 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1125 COALESCE(NEW.parenthesize, FALSE),
1127 COALESCE(NEW.seq_no, 1),
1131 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1132 ON UPDATE TO query.expr_xstr
1134 UPDATE query.expression SET
1136 parenthesize = NEW.parenthesize,
1137 parent_expr = NEW.parent_expr,
1138 seq_no = NEW.seq_no,
1139 literal = NEW.literal
1143 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1144 ON DELETE TO query.expr_xstr
1146 DELETE FROM query.expression WHERE id = OLD.id;
1148 -- Create updatable view for subquery expressions
1150 CREATE OR REPLACE VIEW query.expr_xsubq AS
1163 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1164 ON INSERT TO query.expr_xsubq
1166 INSERT INTO query.expression (
1175 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1177 COALESCE(NEW.parenthesize, FALSE),
1179 COALESCE(NEW.seq_no, 1),
1181 COALESCE(NEW.negate, false)
1184 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1185 ON UPDATE TO query.expr_xsubq
1187 UPDATE query.expression SET
1189 parenthesize = NEW.parenthesize,
1190 parent_expr = NEW.parent_expr,
1191 seq_no = NEW.seq_no,
1192 subquery = NEW.subquery,
1197 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1198 ON DELETE TO query.expr_xsubq
1200 DELETE FROM query.expression WHERE id = OLD.id;