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,
43 limit_count INT, --REFERENCES query.expression
44 --DEFERRABLE INITIALLY DEFERRED,
45 offset_count INT --REFERENCES query.expression
46 --DEFERRABLE INITIALLY DEFERRED
49 -- (Foreign keys to be defined later after other tables are created)
51 CREATE TABLE query.query_sequence (
52 id SERIAL PRIMARY KEY,
53 parent_query INT NOT NULL
54 REFERENCES query.stored_query
56 DEFERRABLE INITIALLY DEFERRED,
58 child_query INT NOT NULL
59 REFERENCES query.stored_query
61 DEFERRABLE INITIALLY DEFERRED,
62 CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
65 CREATE TABLE query.datatype (
66 id SERIAL PRIMARY KEY,
67 datatype_name TEXT NOT NULL UNIQUE,
68 is_numeric BOOL NOT NULL DEFAULT FALSE,
69 is_composite BOOL NOT NULL DEFAULT FALSE,
70 CONSTRAINT qdt_comp_not_num CHECK
71 ( is_numeric IS FALSE OR is_composite IS FALSE )
74 -- Leave room to seed with stock datatypes
75 -- before adding customized ones
76 SELECT setval( 'query.datatype_id_seq', 1000 );
78 CREATE TABLE query.subfield (
79 id SERIAL PRIMARY KEY,
80 composite_type INT NOT NULL
81 REFERENCES query.datatype(id)
83 DEFERRABLE INITIALLY DEFERRED,
85 CONSTRAINT qsf_pos_seq_no
87 subfield_type INT NOT NULL
88 REFERENCES query.datatype(id)
89 DEFERRABLE INITIALLY DEFERRED,
90 CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
93 CREATE TABLE query.function_sig (
94 id SERIAL PRIMARY KEY,
95 function_name TEXT NOT NULL,
96 return_type INT REFERENCES query.datatype(id)
97 DEFERRABLE INITIALLY DEFERRED,
98 is_aggregate BOOL NOT NULL DEFAULT FALSE,
99 CONSTRAINT qfd_rtn_or_aggr CHECK
100 ( return_type IS NULL OR is_aggregate = FALSE )
103 CREATE INDEX query_function_sig_name_idx
104 ON query.function_sig (function_name);
106 CREATE TABLE query.function_param_def (
107 id SERIAL PRIMARY KEY,
108 function_id INT NOT NULL
109 REFERENCES query.function_sig( id )
111 DEFERRABLE INITIALLY DEFERRED,
113 CONSTRAINT qfpd_pos_seq_no CHECK
115 datatype INT NOT NULL
116 REFERENCES query.datatype( id )
117 DEFERRABLE INITIALLY DEFERRED,
118 CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
121 CREATE TABLE query.bind_variable (
122 name TEXT PRIMARY KEY,
124 CONSTRAINT bind_variable_type CHECK
125 ( type in ( 'string', 'number', 'string_list', 'number_list' )),
126 description TEXT NOT NULL,
127 default_value TEXT, -- to be encoded in JSON
131 CREATE TABLE query.expression (
132 id SERIAL PRIMARY KEY,
133 type TEXT NOT NULL CONSTRAINT expression_type CHECK
136 'xbind', -- bind variable
144 'xisnull', -- is null
152 parenthesize BOOL NOT NULL DEFAULT FALSE,
153 parent_expr INT REFERENCES query.expression
155 DEFERRABLE INITIALLY DEFERRED,
156 seq_no INT NOT NULL DEFAULT 1,
160 left_operand INT REFERENCES query.expression
161 DEFERRABLE INITIALLY DEFERRED,
163 right_operand INT REFERENCES query.expression
164 DEFERRABLE INITIALLY DEFERRED,
165 function_id INT REFERENCES query.function_sig
166 DEFERRABLE INITIALLY DEFERRED,
167 subquery INT REFERENCES query.stored_query
168 DEFERRABLE INITIALLY DEFERRED,
169 cast_type INT REFERENCES query.datatype
170 DEFERRABLE INITIALLY DEFERRED,
171 negate BOOL NOT NULL DEFAULT FALSE,
172 bind_variable TEXT REFERENCES query.bind_variable
173 DEFERRABLE INITIALLY DEFERRED
176 CREATE UNIQUE INDEX query_expr_parent_seq
177 ON query.expression( parent_expr, seq_no )
178 WHERE parent_expr IS NOT NULL;
180 -- Due to some circular references, the following foreign key definitions
181 -- had to be deferred until query.expression existed:
183 ALTER TABLE query.stored_query
184 ADD FOREIGN KEY ( where_clause )
185 REFERENCES query.expression( id )
186 DEFERRABLE INITIALLY DEFERRED;
188 ALTER TABLE query.stored_query
189 ADD FOREIGN KEY ( having_clause )
190 REFERENCES query.expression( id )
191 DEFERRABLE INITIALLY DEFERRED;
193 ALTER TABLE query.stored_query
194 ADD FOREIGN KEY ( limit_count )
195 REFERENCES query.expression( id )
196 DEFERRABLE INITIALLY DEFERRED;
198 ALTER TABLE query.stored_query
199 ADD FOREIGN KEY ( offset_count )
200 REFERENCES query.expression( id )
201 DEFERRABLE INITIALLY DEFERRED;
203 CREATE TABLE query.case_branch (
204 id SERIAL PRIMARY KEY,
205 parent_expr INT NOT NULL REFERENCES query.expression
207 DEFERRABLE INITIALLY DEFERRED,
209 condition INT REFERENCES query.expression
210 DEFERRABLE INITIALLY DEFERRED,
211 result INT NOT NULL REFERENCES query.expression
212 DEFERRABLE INITIALLY DEFERRED,
213 CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
216 CREATE TABLE query.from_relation (
217 id SERIAL PRIMARY KEY,
218 type TEXT NOT NULL CONSTRAINT relation_type CHECK (
219 type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
222 subquery INT REFERENCES query.stored_query,
223 function_call INT REFERENCES query.expression,
225 parent_relation INT REFERENCES query.from_relation
227 DEFERRABLE INITIALLY DEFERRED,
228 seq_no INT NOT NULL DEFAULT 1,
229 join_type TEXT CONSTRAINT good_join_type CHECK (
230 join_type IS NULL OR join_type IN
231 ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
233 on_clause INT REFERENCES query.expression
234 DEFERRABLE INITIALLY DEFERRED,
235 CONSTRAINT join_or_core CHECK (
236 ( parent_relation IS NULL AND join_type IS NULL
237 AND on_clause IS NULL )
239 ( parent_relation IS NOT NULL AND join_type IS NOT NULL
240 AND on_clause IS NOT NULL )
244 CREATE UNIQUE INDEX from_parent_seq
245 ON query.from_relation( parent_relation, seq_no )
246 WHERE parent_relation IS NOT NULL;
248 -- The following foreign key had to be deferred until
249 -- query.from_relation existed
251 ALTER TABLE query.stored_query
252 ADD FOREIGN KEY (from_clause)
253 REFERENCES query.from_relation
254 DEFERRABLE INITIALLY DEFERRED;
256 CREATE TABLE query.record_column (
257 id SERIAL PRIMARY KEY,
258 from_relation INT NOT NULL REFERENCES query.from_relation
260 DEFERRABLE INITIALLY DEFERRED,
262 column_name TEXT NOT NULL,
263 column_type INT NOT NULL REFERENCES query.datatype
265 DEFERRABLE INITIALLY DEFERRED,
266 CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
269 CREATE TABLE query.select_item (
270 id SERIAL PRIMARY KEY,
271 stored_query INT NOT NULL REFERENCES query.stored_query
273 DEFERRABLE INITIALLY DEFERRED,
275 expression INT NOT NULL REFERENCES query.expression
276 DEFERRABLE INITIALLY DEFERRED,
278 grouped_by BOOL NOT NULL DEFAULT FALSE,
279 CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
282 CREATE TABLE query.order_by_item (
283 id SERIAL PRIMARY KEY,
284 stored_query INT NOT NULL REFERENCES query.stored_query
286 DEFERRABLE INITIALLY DEFERRED,
288 expression INT NOT NULL REFERENCES query.expression
290 DEFERRABLE INITIALLY DEFERRED,
291 CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
294 -- Create updatable views -------------------------------------------
296 -- Create updatable view for BETWEEN expressions
298 CREATE OR REPLACE VIEW query.expr_xbet AS
311 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
312 ON INSERT TO query.expr_xbet
314 INSERT INTO query.expression (
323 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
325 COALESCE(NEW.parenthesize, FALSE),
327 COALESCE(NEW.seq_no, 1),
329 COALESCE(NEW.negate, false)
332 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
333 ON UPDATE TO query.expr_xbet
335 UPDATE query.expression SET
337 parenthesize = NEW.parenthesize,
338 parent_expr = NEW.parent_expr,
340 left_operand = NEW.left_operand,
345 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
346 ON DELETE TO query.expr_xbet
348 DELETE FROM query.expression WHERE id = OLD.id;
350 -- Create updatable view for bind variable expressions
352 CREATE OR REPLACE VIEW query.expr_xbind AS
364 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
365 ON INSERT TO query.expr_xbind
367 INSERT INTO query.expression (
375 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
377 COALESCE(NEW.parenthesize, FALSE),
379 COALESCE(NEW.seq_no, 1),
383 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
384 ON UPDATE TO query.expr_xbind
386 UPDATE query.expression SET
388 parenthesize = NEW.parenthesize,
389 parent_expr = NEW.parent_expr,
391 bind_variable = NEW.bind_variable
395 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
396 ON DELETE TO query.expr_xbind
398 DELETE FROM query.expression WHERE id = OLD.id;
400 -- Create updatable view for boolean expressions
402 CREATE OR REPLACE VIEW query.expr_xbool AS
415 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
416 ON INSERT TO query.expr_xbool
418 INSERT INTO query.expression (
427 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
429 COALESCE(NEW.parenthesize, FALSE),
431 COALESCE(NEW.seq_no, 1),
433 COALESCE(NEW.negate, false)
436 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
437 ON UPDATE TO query.expr_xbool
439 UPDATE query.expression SET
441 parenthesize = NEW.parenthesize,
442 parent_expr = NEW.parent_expr,
444 literal = NEW.literal,
449 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
450 ON DELETE TO query.expr_xbool
452 DELETE FROM query.expression WHERE id = OLD.id;
454 -- Create updatable view for CASE expressions
456 CREATE OR REPLACE VIEW query.expr_xcase AS
469 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
470 ON INSERT TO query.expr_xcase
472 INSERT INTO query.expression (
481 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
483 COALESCE(NEW.parenthesize, FALSE),
485 COALESCE(NEW.seq_no, 1),
487 COALESCE(NEW.negate, false)
490 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
491 ON UPDATE TO query.expr_xcase
493 UPDATE query.expression SET
495 parenthesize = NEW.parenthesize,
496 parent_expr = NEW.parent_expr,
498 left_operand = NEW.left_operand,
503 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
504 ON DELETE TO query.expr_xcase
506 DELETE FROM query.expression WHERE id = OLD.id;
508 -- Create updatable view for cast expressions
510 CREATE OR REPLACE VIEW query.expr_xcast AS
524 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
525 ON INSERT TO query.expr_xcast
527 INSERT INTO query.expression (
537 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
539 COALESCE(NEW.parenthesize, FALSE),
541 COALESCE(NEW.seq_no, 1),
544 COALESCE(NEW.negate, false)
547 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
548 ON UPDATE TO query.expr_xcast
550 UPDATE query.expression SET
552 parenthesize = NEW.parenthesize,
553 parent_expr = NEW.parent_expr,
555 left_operand = NEW.left_operand,
556 cast_type = NEW.cast_type,
561 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
562 ON DELETE TO query.expr_xcast
564 DELETE FROM query.expression WHERE id = OLD.id;
566 -- Create updatable view for column expressions
568 CREATE OR REPLACE VIEW query.expr_xcol AS
582 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
583 ON INSERT TO query.expr_xcol
585 INSERT INTO query.expression (
595 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
597 COALESCE(NEW.parenthesize, FALSE),
599 COALESCE(NEW.seq_no, 1),
602 COALESCE(NEW.negate, false)
605 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
606 ON UPDATE TO query.expr_xcol
608 UPDATE query.expression SET
610 parenthesize = NEW.parenthesize,
611 parent_expr = NEW.parent_expr,
613 table_alias = NEW.table_alias,
614 column_name = NEW.column_name,
619 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
620 ON DELETE TO query.expr_xcol
622 DELETE FROM query.expression WHERE id = OLD.id;
624 -- Create updatable view for EXISTS expressions
626 CREATE OR REPLACE VIEW query.expr_xex AS
639 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
640 ON INSERT TO query.expr_xex
642 INSERT INTO query.expression (
651 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
653 COALESCE(NEW.parenthesize, FALSE),
655 COALESCE(NEW.seq_no, 1),
657 COALESCE(NEW.negate, false)
660 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
661 ON UPDATE TO query.expr_xex
663 UPDATE query.expression SET
665 parenthesize = NEW.parenthesize,
666 parent_expr = NEW.parent_expr,
668 subquery = NEW.subquery,
673 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
674 ON DELETE TO query.expr_xex
676 DELETE FROM query.expression WHERE id = OLD.id;
678 -- Create updatable view for function call expressions
680 CREATE OR REPLACE VIEW query.expr_xfunc AS
694 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
695 ON INSERT TO query.expr_xfunc
697 INSERT INTO query.expression (
707 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
709 COALESCE(NEW.parenthesize, FALSE),
711 COALESCE(NEW.seq_no, 1),
714 COALESCE(NEW.negate, false)
717 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
718 ON UPDATE TO query.expr_xfunc
720 UPDATE query.expression SET
722 parenthesize = NEW.parenthesize,
723 parent_expr = NEW.parent_expr,
725 column_name = NEW.column_name,
726 function_id = NEW.function_id,
731 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
732 ON DELETE TO query.expr_xfunc
734 DELETE FROM query.expression WHERE id = OLD.id;
736 -- Create updatable view for IN expressions
738 CREATE OR REPLACE VIEW query.expr_xin AS
752 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
753 ON INSERT TO query.expr_xin
755 INSERT INTO query.expression (
765 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
767 COALESCE(NEW.parenthesize, FALSE),
769 COALESCE(NEW.seq_no, 1),
772 COALESCE(NEW.negate, false)
775 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
776 ON UPDATE TO query.expr_xin
778 UPDATE query.expression SET
780 parenthesize = NEW.parenthesize,
781 parent_expr = NEW.parent_expr,
783 left_operand = NEW.left_operand,
784 subquery = NEW.subquery,
789 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
790 ON DELETE TO query.expr_xin
792 DELETE FROM query.expression WHERE id = OLD.id;
794 -- Create updatable view for IS NULL expressions
796 CREATE OR REPLACE VIEW query.expr_xisnull AS
809 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
810 ON INSERT TO query.expr_xisnull
812 INSERT INTO query.expression (
821 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
823 COALESCE(NEW.parenthesize, FALSE),
825 COALESCE(NEW.seq_no, 1),
827 COALESCE(NEW.negate, false)
830 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
831 ON UPDATE TO query.expr_xisnull
833 UPDATE query.expression SET
835 parenthesize = NEW.parenthesize,
836 parent_expr = NEW.parent_expr,
838 left_operand = NEW.left_operand,
843 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
844 ON DELETE TO query.expr_xisnull
846 DELETE FROM query.expression WHERE id = OLD.id;
848 -- Create updatable view for NULL expressions
850 CREATE OR REPLACE VIEW query.expr_xnull AS
862 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
863 ON INSERT TO query.expr_xnull
865 INSERT INTO query.expression (
873 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
875 COALESCE(NEW.parenthesize, FALSE),
877 COALESCE(NEW.seq_no, 1),
878 COALESCE(NEW.negate, false)
881 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
882 ON UPDATE TO query.expr_xnull
884 UPDATE query.expression SET
886 parenthesize = NEW.parenthesize,
887 parent_expr = NEW.parent_expr,
893 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
894 ON DELETE TO query.expr_xnull
896 DELETE FROM query.expression WHERE id = OLD.id;
898 -- Create updatable view for numeric literal expressions
900 CREATE OR REPLACE VIEW query.expr_xnum AS
912 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
913 ON INSERT TO query.expr_xnum
915 INSERT INTO query.expression (
923 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
925 COALESCE(NEW.parenthesize, FALSE),
927 COALESCE(NEW.seq_no, 1),
931 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
932 ON UPDATE TO query.expr_xnum
934 UPDATE query.expression SET
936 parenthesize = NEW.parenthesize,
937 parent_expr = NEW.parent_expr,
939 literal = NEW.literal
943 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
944 ON DELETE TO query.expr_xnum
946 DELETE FROM query.expression WHERE id = OLD.id;
948 -- Create updatable view for operator expressions
950 CREATE OR REPLACE VIEW query.expr_xop AS
965 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
966 ON INSERT TO query.expr_xop
968 INSERT INTO query.expression (
979 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
981 COALESCE(NEW.parenthesize, FALSE),
983 COALESCE(NEW.seq_no, 1),
987 COALESCE(NEW.negate, false)
990 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
991 ON UPDATE TO query.expr_xop
993 UPDATE query.expression SET
995 parenthesize = NEW.parenthesize,
996 parent_expr = NEW.parent_expr,
998 left_operand = NEW.left_operand,
999 operator = NEW.operator,
1000 right_operand = NEW.right_operand,
1005 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1006 ON DELETE TO query.expr_xop
1008 DELETE FROM query.expression WHERE id = OLD.id;
1010 -- Create updatable view for series expressions,
1011 -- i.e. series of expressions separated by operators
1013 CREATE OR REPLACE VIEW query.expr_xser AS
1026 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1027 ON INSERT TO query.expr_xser
1029 INSERT INTO query.expression (
1038 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1040 COALESCE(NEW.parenthesize, FALSE),
1042 COALESCE(NEW.seq_no, 1),
1044 COALESCE(NEW.negate, false)
1047 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1048 ON UPDATE TO query.expr_xser
1050 UPDATE query.expression SET
1052 parenthesize = NEW.parenthesize,
1053 parent_expr = NEW.parent_expr,
1054 seq_no = NEW.seq_no,
1055 operator = NEW.operator,
1060 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1061 ON DELETE TO query.expr_xser
1063 DELETE FROM query.expression WHERE id = OLD.id;
1065 -- Create updatable view for string literal expressions
1067 CREATE OR REPLACE VIEW query.expr_xstr AS
1079 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1080 ON INSERT TO query.expr_xstr
1082 INSERT INTO query.expression (
1090 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1092 COALESCE(NEW.parenthesize, FALSE),
1094 COALESCE(NEW.seq_no, 1),
1098 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1099 ON UPDATE TO query.expr_xstr
1101 UPDATE query.expression SET
1103 parenthesize = NEW.parenthesize,
1104 parent_expr = NEW.parent_expr,
1105 seq_no = NEW.seq_no,
1106 literal = NEW.literal
1110 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1111 ON DELETE TO query.expr_xstr
1113 DELETE FROM query.expression WHERE id = OLD.id;
1115 -- Create updatable view for subquery expressions
1117 CREATE OR REPLACE VIEW query.expr_xsubq AS
1130 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1131 ON INSERT TO query.expr_xsubq
1133 INSERT INTO query.expression (
1142 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1144 COALESCE(NEW.parenthesize, FALSE),
1146 COALESCE(NEW.seq_no, 1),
1148 COALESCE(NEW.negate, false)
1151 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1152 ON UPDATE TO query.expr_xsubq
1154 UPDATE query.expression SET
1156 parenthesize = NEW.parenthesize,
1157 parent_expr = NEW.parent_expr,
1158 seq_no = NEW.seq_no,
1159 subquery = NEW.subquery,
1164 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1165 ON DELETE TO query.expr_xsubq
1167 DELETE FROM query.expression WHERE id = OLD.id;