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
293 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
294 ON INSERT TO query.expr_xbet
296 INSERT INTO query.expression (
304 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
306 COALESCE(NEW.parenthesize, FALSE),
308 COALESCE(NEW.seq_no, 1),
309 COALESCE(NEW.negate, false)
312 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
313 ON UPDATE TO query.expr_xbet
315 UPDATE query.expression SET
317 parenthesize = NEW.parenthesize,
318 parent_expr = NEW.parent_expr,
324 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
325 ON DELETE TO query.expr_xbet
327 DELETE FROM query.expression WHERE id = OLD.id;
329 -- Create updatable view for bind variable expressions
331 CREATE OR REPLACE VIEW query.expr_xbind AS
343 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
344 ON INSERT TO query.expr_xbind
346 INSERT INTO query.expression (
354 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
356 COALESCE(NEW.parenthesize, FALSE),
358 COALESCE(NEW.seq_no, 1),
362 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
363 ON UPDATE TO query.expr_xbind
365 UPDATE query.expression SET
367 parenthesize = NEW.parenthesize,
368 parent_expr = NEW.parent_expr,
370 bind_variable = NEW.bind_variable
374 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
375 ON DELETE TO query.expr_xbind
377 DELETE FROM query.expression WHERE id = OLD.id;
379 -- Create updatable view for boolean expressions
381 CREATE OR REPLACE VIEW query.expr_xbool AS
394 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
395 ON INSERT TO query.expr_xbool
397 INSERT INTO query.expression (
406 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
408 COALESCE(NEW.parenthesize, FALSE),
410 COALESCE(NEW.seq_no, 1),
412 COALESCE(NEW.negate, false)
415 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
416 ON UPDATE TO query.expr_xbool
418 UPDATE query.expression SET
420 parenthesize = NEW.parenthesize,
421 parent_expr = NEW.parent_expr,
423 literal = NEW.literal,
428 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
429 ON DELETE TO query.expr_xbool
431 DELETE FROM query.expression WHERE id = OLD.id;
433 -- Create updatable view for CASE expressions
435 CREATE OR REPLACE VIEW query.expr_xcase AS
447 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
448 ON INSERT TO query.expr_xcase
450 INSERT INTO query.expression (
458 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
460 COALESCE(NEW.parenthesize, FALSE),
462 COALESCE(NEW.seq_no, 1),
463 COALESCE(NEW.negate, false)
466 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
467 ON UPDATE TO query.expr_xcase
469 UPDATE query.expression SET
471 parenthesize = NEW.parenthesize,
472 parent_expr = NEW.parent_expr,
478 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
479 ON DELETE TO query.expr_xcase
481 DELETE FROM query.expression WHERE id = OLD.id;
483 -- Create updatable view for cast expressions
485 CREATE OR REPLACE VIEW query.expr_xcast AS
499 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
500 ON INSERT TO query.expr_xcast
502 INSERT INTO query.expression (
512 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
514 COALESCE(NEW.parenthesize, FALSE),
516 COALESCE(NEW.seq_no, 1),
519 COALESCE(NEW.negate, false)
522 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
523 ON UPDATE TO query.expr_xcast
525 UPDATE query.expression SET
527 parenthesize = NEW.parenthesize,
528 parent_expr = NEW.parent_expr,
530 left_operand = NEW.left_operand,
531 cast_type = NEW.cast_type,
536 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
537 ON DELETE TO query.expr_xcast
539 DELETE FROM query.expression WHERE id = OLD.id;
541 -- Create updatable view for column expressions
543 CREATE OR REPLACE VIEW query.expr_xcol AS
557 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
558 ON INSERT TO query.expr_xcol
560 INSERT INTO query.expression (
570 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
572 COALESCE(NEW.parenthesize, FALSE),
574 COALESCE(NEW.seq_no, 1),
577 COALESCE(NEW.negate, false)
580 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
581 ON UPDATE TO query.expr_xcol
583 UPDATE query.expression SET
585 parenthesize = NEW.parenthesize,
586 parent_expr = NEW.parent_expr,
588 table_alias = NEW.table_alias,
589 column_name = NEW.column_name,
594 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
595 ON DELETE TO query.expr_xcol
597 DELETE FROM query.expression WHERE id = OLD.id;
599 -- Create updatable view for EXISTS expressions
601 CREATE OR REPLACE VIEW query.expr_xex AS
614 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
615 ON INSERT TO query.expr_xex
617 INSERT INTO query.expression (
626 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
628 COALESCE(NEW.parenthesize, FALSE),
630 COALESCE(NEW.seq_no, 1),
632 COALESCE(NEW.negate, false)
635 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
636 ON UPDATE TO query.expr_xex
638 UPDATE query.expression SET
640 parenthesize = NEW.parenthesize,
641 parent_expr = NEW.parent_expr,
643 subquery = NEW.subquery,
648 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
649 ON DELETE TO query.expr_xex
651 DELETE FROM query.expression WHERE id = OLD.id;
653 -- Create updatable view for field expressions
655 CREATE OR REPLACE VIEW query.expr_xfld AS
669 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
670 ON INSERT TO query.expr_xfld
672 INSERT INTO query.expression (
682 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
684 COALESCE(NEW.parenthesize, FALSE),
686 COALESCE(NEW.seq_no, 1),
689 COALESCE(NEW.negate, false)
692 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
693 ON UPDATE TO query.expr_xfld
695 UPDATE query.expression SET
697 parenthesize = NEW.parenthesize,
698 parent_expr = NEW.parent_expr,
700 column_name = NEW.column_name,
701 left_operand = NEW.left_operand,
706 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
707 ON DELETE TO query.expr_xfld
709 DELETE FROM query.expression WHERE id = OLD.id;
711 -- Create updatable view for function call expressions
713 CREATE OR REPLACE VIEW query.expr_xfunc AS
726 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
727 ON INSERT TO query.expr_xfunc
729 INSERT INTO query.expression (
738 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
740 COALESCE(NEW.parenthesize, FALSE),
742 COALESCE(NEW.seq_no, 1),
744 COALESCE(NEW.negate, false)
747 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
748 ON UPDATE TO query.expr_xfunc
750 UPDATE query.expression SET
752 parenthesize = NEW.parenthesize,
753 parent_expr = NEW.parent_expr,
755 function_id = NEW.function_id,
760 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
761 ON DELETE TO query.expr_xfunc
763 DELETE FROM query.expression WHERE id = OLD.id;
765 -- Create updatable view for IN expressions
767 CREATE OR REPLACE VIEW query.expr_xin AS
781 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
782 ON INSERT TO query.expr_xin
784 INSERT INTO query.expression (
794 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
796 COALESCE(NEW.parenthesize, FALSE),
798 COALESCE(NEW.seq_no, 1),
801 COALESCE(NEW.negate, false)
804 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
805 ON UPDATE TO query.expr_xin
807 UPDATE query.expression SET
809 parenthesize = NEW.parenthesize,
810 parent_expr = NEW.parent_expr,
812 left_operand = NEW.left_operand,
813 subquery = NEW.subquery,
818 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
819 ON DELETE TO query.expr_xin
821 DELETE FROM query.expression WHERE id = OLD.id;
823 -- Create updatable view for IS NULL expressions
825 CREATE OR REPLACE VIEW query.expr_xisnull AS
838 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
839 ON INSERT TO query.expr_xisnull
841 INSERT INTO query.expression (
850 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
852 COALESCE(NEW.parenthesize, FALSE),
854 COALESCE(NEW.seq_no, 1),
856 COALESCE(NEW.negate, false)
859 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
860 ON UPDATE TO query.expr_xisnull
862 UPDATE query.expression SET
864 parenthesize = NEW.parenthesize,
865 parent_expr = NEW.parent_expr,
867 left_operand = NEW.left_operand,
872 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
873 ON DELETE TO query.expr_xisnull
875 DELETE FROM query.expression WHERE id = OLD.id;
877 -- Create updatable view for NULL expressions
879 CREATE OR REPLACE VIEW query.expr_xnull AS
891 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
892 ON INSERT TO query.expr_xnull
894 INSERT INTO query.expression (
902 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
904 COALESCE(NEW.parenthesize, FALSE),
906 COALESCE(NEW.seq_no, 1),
907 COALESCE(NEW.negate, false)
910 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
911 ON UPDATE TO query.expr_xnull
913 UPDATE query.expression SET
915 parenthesize = NEW.parenthesize,
916 parent_expr = NEW.parent_expr,
922 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
923 ON DELETE TO query.expr_xnull
925 DELETE FROM query.expression WHERE id = OLD.id;
927 -- Create updatable view for numeric literal expressions
929 CREATE OR REPLACE VIEW query.expr_xnum AS
941 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
942 ON INSERT TO query.expr_xnum
944 INSERT INTO query.expression (
952 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
954 COALESCE(NEW.parenthesize, FALSE),
956 COALESCE(NEW.seq_no, 1),
960 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
961 ON UPDATE TO query.expr_xnum
963 UPDATE query.expression SET
965 parenthesize = NEW.parenthesize,
966 parent_expr = NEW.parent_expr,
968 literal = NEW.literal
972 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
973 ON DELETE TO query.expr_xnum
975 DELETE FROM query.expression WHERE id = OLD.id;
977 -- Create updatable view for operator expressions
979 CREATE OR REPLACE VIEW query.expr_xop AS
994 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
995 ON INSERT TO query.expr_xop
997 INSERT INTO query.expression (
1008 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1010 COALESCE(NEW.parenthesize, FALSE),
1012 COALESCE(NEW.seq_no, 1),
1016 COALESCE(NEW.negate, false)
1019 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
1020 ON UPDATE TO query.expr_xop
1022 UPDATE query.expression SET
1024 parenthesize = NEW.parenthesize,
1025 parent_expr = NEW.parent_expr,
1026 seq_no = NEW.seq_no,
1027 left_operand = NEW.left_operand,
1028 operator = NEW.operator,
1029 right_operand = NEW.right_operand,
1034 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1035 ON DELETE TO query.expr_xop
1037 DELETE FROM query.expression WHERE id = OLD.id;
1039 -- Create updatable view for series expressions,
1040 -- i.e. series of expressions separated by operators
1042 CREATE OR REPLACE VIEW query.expr_xser AS
1055 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1056 ON INSERT TO query.expr_xser
1058 INSERT INTO query.expression (
1067 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1069 COALESCE(NEW.parenthesize, FALSE),
1071 COALESCE(NEW.seq_no, 1),
1073 COALESCE(NEW.negate, false)
1076 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1077 ON UPDATE TO query.expr_xser
1079 UPDATE query.expression SET
1081 parenthesize = NEW.parenthesize,
1082 parent_expr = NEW.parent_expr,
1083 seq_no = NEW.seq_no,
1084 operator = NEW.operator,
1089 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1090 ON DELETE TO query.expr_xser
1092 DELETE FROM query.expression WHERE id = OLD.id;
1094 -- Create updatable view for string literal expressions
1096 CREATE OR REPLACE VIEW query.expr_xstr AS
1108 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1109 ON INSERT TO query.expr_xstr
1111 INSERT INTO query.expression (
1119 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1121 COALESCE(NEW.parenthesize, FALSE),
1123 COALESCE(NEW.seq_no, 1),
1127 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1128 ON UPDATE TO query.expr_xstr
1130 UPDATE query.expression SET
1132 parenthesize = NEW.parenthesize,
1133 parent_expr = NEW.parent_expr,
1134 seq_no = NEW.seq_no,
1135 literal = NEW.literal
1139 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1140 ON DELETE TO query.expr_xstr
1142 DELETE FROM query.expression WHERE id = OLD.id;
1144 -- Create updatable view for subquery expressions
1146 CREATE OR REPLACE VIEW query.expr_xsubq AS
1159 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1160 ON INSERT TO query.expr_xsubq
1162 INSERT INTO query.expression (
1171 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1173 COALESCE(NEW.parenthesize, FALSE),
1175 COALESCE(NEW.seq_no, 1),
1177 COALESCE(NEW.negate, false)
1180 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1181 ON UPDATE TO query.expr_xsubq
1183 UPDATE query.expression SET
1185 parenthesize = NEW.parenthesize,
1186 parent_expr = NEW.parent_expr,
1187 seq_no = NEW.seq_no,
1188 subquery = NEW.subquery,
1193 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1194 ON DELETE TO query.expr_xsubq
1196 DELETE FROM query.expression WHERE id = OLD.id;