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 -- Leave room to seed with stock datatypes
71 -- before adding customized ones
72 SELECT setval( 'query.datatype_id_seq', 1000 );
74 CREATE TABLE query.subfield (
75 id SERIAL PRIMARY KEY,
76 composite_type INT NOT NULL
77 REFERENCES query.datatype(id)
79 DEFERRABLE INITIALLY DEFERRED,
81 CONSTRAINT qsf_pos_seq_no
83 subfield_type INT NOT NULL
84 REFERENCES query.datatype(id)
85 DEFERRABLE INITIALLY DEFERRED,
86 CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
89 CREATE TABLE query.function_sig (
90 id SERIAL PRIMARY KEY,
91 function_name TEXT NOT NULL,
92 return_type INT REFERENCES query.datatype(id)
93 DEFERRABLE INITIALLY DEFERRED,
94 is_aggregate BOOL NOT NULL DEFAULT FALSE,
95 CONSTRAINT qfd_rtn_or_aggr CHECK
96 ( return_type IS NULL OR is_aggregate = FALSE )
99 CREATE INDEX query_function_sig_name_idx
100 ON query.function_sig (function_name);
102 CREATE TABLE query.function_param_def (
103 id SERIAL PRIMARY KEY,
104 function_id INT NOT NULL
105 REFERENCES query.function_sig( id )
107 DEFERRABLE INITIALLY DEFERRED,
109 CONSTRAINT qfpd_pos_seq_no CHECK
111 datatype INT NOT NULL
112 REFERENCES query.datatype( id )
113 DEFERRABLE INITIALLY DEFERRED,
114 CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
117 CREATE TABLE query.bind_variable (
118 name TEXT PRIMARY KEY,
120 CONSTRAINT bind_variable_type CHECK
121 ( type in ( 'string', 'number', 'string_list', 'number_list' )),
122 description TEXT NOT NULL,
123 default_value TEXT, -- to be encoded in JSON
127 CREATE TABLE query.expression (
128 id SERIAL PRIMARY KEY,
129 type TEXT NOT NULL CONSTRAINT expression_type CHECK
132 'xbind', -- bind variable
141 'xisnull', -- is null
149 parenthesize BOOL NOT NULL DEFAULT FALSE,
150 parent_expr INT REFERENCES query.expression
152 DEFERRABLE INITIALLY DEFERRED,
153 seq_no INT NOT NULL DEFAULT 1,
157 left_operand INT REFERENCES query.expression
158 DEFERRABLE INITIALLY DEFERRED,
160 right_operand INT REFERENCES query.expression
161 DEFERRABLE INITIALLY DEFERRED,
162 function_id INT REFERENCES query.function_sig
163 DEFERRABLE INITIALLY DEFERRED,
164 subquery INT REFERENCES query.stored_query
165 DEFERRABLE INITIALLY DEFERRED,
166 cast_type INT REFERENCES query.datatype
167 DEFERRABLE INITIALLY DEFERRED,
168 negate BOOL NOT NULL DEFAULT FALSE,
169 bind_variable TEXT REFERENCES query.bind_variable
170 DEFERRABLE INITIALLY DEFERRED
173 CREATE UNIQUE INDEX query_expr_parent_seq
174 ON query.expression( parent_expr, seq_no )
175 WHERE parent_expr IS NOT NULL;
177 -- Due to some circular references, the following foreign key definitions
178 -- had to be deferred until query.expression existed:
180 ALTER TABLE query.stored_query
181 ADD FOREIGN KEY ( where_clause )
182 REFERENCES query.expression( id )
183 DEFERRABLE INITIALLY DEFERRED;
185 ALTER TABLE query.stored_query
186 ADD FOREIGN KEY ( having_clause )
187 REFERENCES query.expression( id )
188 DEFERRABLE INITIALLY DEFERRED;
190 CREATE TABLE query.case_branch (
191 id SERIAL PRIMARY KEY,
192 parent_expr INT NOT NULL REFERENCES query.expression
194 DEFERRABLE INITIALLY DEFERRED,
196 condition INT REFERENCES query.expression
197 DEFERRABLE INITIALLY DEFERRED,
198 result INT NOT NULL REFERENCES query.expression
199 DEFERRABLE INITIALLY DEFERRED,
200 CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
203 CREATE TABLE query.from_relation (
204 id SERIAL PRIMARY KEY,
205 type TEXT NOT NULL CONSTRAINT relation_type CHECK (
206 type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
209 subquery INT REFERENCES query.stored_query,
210 function_call INT REFERENCES query.expression,
212 parent_relation INT REFERENCES query.from_relation
214 DEFERRABLE INITIALLY DEFERRED,
215 seq_no INT NOT NULL DEFAULT 1,
216 join_type TEXT CONSTRAINT good_join_type CHECK (
217 join_type IS NULL OR join_type IN
218 ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
220 on_clause INT REFERENCES query.expression
221 DEFERRABLE INITIALLY DEFERRED,
222 CONSTRAINT join_or_core CHECK (
223 ( parent_relation IS NULL AND join_type IS NULL
224 AND on_clause IS NULL )
226 ( parent_relation IS NOT NULL AND join_type IS NOT NULL
227 AND on_clause IS NOT NULL )
231 CREATE UNIQUE INDEX from_parent_seq
232 ON query.from_relation( parent_relation, seq_no )
233 WHERE parent_relation IS NOT NULL;
235 -- The following foreign key had to be deferred until
236 -- query.from_relation existed
238 ALTER TABLE query.stored_query
239 ADD FOREIGN KEY (from_clause)
240 REFERENCES query.from_relation
241 DEFERRABLE INITIALLY DEFERRED;
243 CREATE TABLE query.record_column (
244 id SERIAL PRIMARY KEY,
245 from_relation INT NOT NULL REFERENCES query.from_relation
247 DEFERRABLE INITIALLY DEFERRED,
249 column_name TEXT NOT NULL,
250 column_type INT NOT NULL REFERENCES query.datatype
252 DEFERRABLE INITIALLY DEFERRED,
253 CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
256 CREATE TABLE query.select_item (
257 id SERIAL PRIMARY KEY,
258 stored_query INT NOT NULL REFERENCES query.stored_query
260 DEFERRABLE INITIALLY DEFERRED,
262 expression INT NOT NULL REFERENCES query.expression
263 DEFERRABLE INITIALLY DEFERRED,
265 grouped_by BOOL NOT NULL DEFAULT FALSE,
266 CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
269 CREATE TABLE query.order_by_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
277 DEFERRABLE INITIALLY DEFERRED,
278 CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
281 -- Create updatable views -------------------------------------------
283 -- Create updatable view for BETWEEN expressions
285 CREATE OR REPLACE VIEW query.expr_xbet AS
298 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
299 ON INSERT TO query.expr_xbet
301 INSERT INTO query.expression (
310 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
312 COALESCE(NEW.parenthesize, FALSE),
314 COALESCE(NEW.seq_no, 1),
316 COALESCE(NEW.negate, false)
319 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
320 ON UPDATE TO query.expr_xbet
322 UPDATE query.expression SET
324 parenthesize = NEW.parenthesize,
325 parent_expr = NEW.parent_expr,
327 left_operand = NEW.left_operand,
332 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
333 ON DELETE TO query.expr_xbet
335 DELETE FROM query.expression WHERE id = OLD.id;
337 -- Create updatable view for bind variable expressions
339 CREATE OR REPLACE VIEW query.expr_xbind AS
351 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
352 ON INSERT TO query.expr_xbind
354 INSERT INTO query.expression (
362 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
364 COALESCE(NEW.parenthesize, FALSE),
366 COALESCE(NEW.seq_no, 1),
370 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
371 ON UPDATE TO query.expr_xbind
373 UPDATE query.expression SET
375 parenthesize = NEW.parenthesize,
376 parent_expr = NEW.parent_expr,
378 bind_variable = NEW.bind_variable
382 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
383 ON DELETE TO query.expr_xbind
385 DELETE FROM query.expression WHERE id = OLD.id;
387 -- Create updatable view for boolean expressions
389 CREATE OR REPLACE VIEW query.expr_xbool AS
402 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
403 ON INSERT TO query.expr_xbool
405 INSERT INTO query.expression (
414 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
416 COALESCE(NEW.parenthesize, FALSE),
418 COALESCE(NEW.seq_no, 1),
420 COALESCE(NEW.negate, false)
423 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
424 ON UPDATE TO query.expr_xbool
426 UPDATE query.expression SET
428 parenthesize = NEW.parenthesize,
429 parent_expr = NEW.parent_expr,
431 literal = NEW.literal,
436 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
437 ON DELETE TO query.expr_xbool
439 DELETE FROM query.expression WHERE id = OLD.id;
441 -- Create updatable view for CASE expressions
443 CREATE OR REPLACE VIEW query.expr_xcase AS
456 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
457 ON INSERT TO query.expr_xcase
459 INSERT INTO query.expression (
468 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
470 COALESCE(NEW.parenthesize, FALSE),
472 COALESCE(NEW.seq_no, 1),
474 COALESCE(NEW.negate, false)
477 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
478 ON UPDATE TO query.expr_xcase
480 UPDATE query.expression SET
482 parenthesize = NEW.parenthesize,
483 parent_expr = NEW.parent_expr,
485 left_operand = NEW.left_operand,
490 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
491 ON DELETE TO query.expr_xcase
493 DELETE FROM query.expression WHERE id = OLD.id;
495 -- Create updatable view for cast expressions
497 CREATE OR REPLACE VIEW query.expr_xcast AS
511 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
512 ON INSERT TO query.expr_xcast
514 INSERT INTO query.expression (
524 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
526 COALESCE(NEW.parenthesize, FALSE),
528 COALESCE(NEW.seq_no, 1),
531 COALESCE(NEW.negate, false)
534 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
535 ON UPDATE TO query.expr_xcast
537 UPDATE query.expression SET
539 parenthesize = NEW.parenthesize,
540 parent_expr = NEW.parent_expr,
542 left_operand = NEW.left_operand,
543 cast_type = NEW.cast_type,
548 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
549 ON DELETE TO query.expr_xcast
551 DELETE FROM query.expression WHERE id = OLD.id;
553 -- Create updatable view for column expressions
555 CREATE OR REPLACE VIEW query.expr_xcol AS
569 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
570 ON INSERT TO query.expr_xcol
572 INSERT INTO query.expression (
582 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
584 COALESCE(NEW.parenthesize, FALSE),
586 COALESCE(NEW.seq_no, 1),
589 COALESCE(NEW.negate, false)
592 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
593 ON UPDATE TO query.expr_xcol
595 UPDATE query.expression SET
597 parenthesize = NEW.parenthesize,
598 parent_expr = NEW.parent_expr,
600 table_alias = NEW.table_alias,
601 column_name = NEW.column_name,
606 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
607 ON DELETE TO query.expr_xcol
609 DELETE FROM query.expression WHERE id = OLD.id;
611 -- Create updatable view for EXISTS expressions
613 CREATE OR REPLACE VIEW query.expr_xex AS
626 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
627 ON INSERT TO query.expr_xex
629 INSERT INTO query.expression (
638 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
640 COALESCE(NEW.parenthesize, FALSE),
642 COALESCE(NEW.seq_no, 1),
644 COALESCE(NEW.negate, false)
647 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
648 ON UPDATE TO query.expr_xex
650 UPDATE query.expression SET
652 parenthesize = NEW.parenthesize,
653 parent_expr = NEW.parent_expr,
655 subquery = NEW.subquery,
660 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
661 ON DELETE TO query.expr_xex
663 DELETE FROM query.expression WHERE id = OLD.id;
665 -- Create updatable view for field expressions
667 CREATE OR REPLACE VIEW query.expr_xfld AS
681 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
682 ON INSERT TO query.expr_xfld
684 INSERT INTO query.expression (
694 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
696 COALESCE(NEW.parenthesize, FALSE),
698 COALESCE(NEW.seq_no, 1),
701 COALESCE(NEW.negate, false)
704 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
705 ON UPDATE TO query.expr_xfld
707 UPDATE query.expression SET
709 parenthesize = NEW.parenthesize,
710 parent_expr = NEW.parent_expr,
712 column_name = NEW.column_name,
713 left_operand = NEW.left_operand,
718 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
719 ON DELETE TO query.expr_xfld
721 DELETE FROM query.expression WHERE id = OLD.id;
723 -- Create updatable view for function call expressions
725 CREATE OR REPLACE VIEW query.expr_xfunc AS
738 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
739 ON INSERT TO query.expr_xfunc
741 INSERT INTO query.expression (
750 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
752 COALESCE(NEW.parenthesize, FALSE),
754 COALESCE(NEW.seq_no, 1),
756 COALESCE(NEW.negate, false)
759 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
760 ON UPDATE TO query.expr_xfunc
762 UPDATE query.expression SET
764 parenthesize = NEW.parenthesize,
765 parent_expr = NEW.parent_expr,
767 function_id = NEW.function_id,
772 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
773 ON DELETE TO query.expr_xfunc
775 DELETE FROM query.expression WHERE id = OLD.id;
777 -- Create updatable view for IN expressions
779 CREATE OR REPLACE VIEW query.expr_xin AS
793 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
794 ON INSERT TO query.expr_xin
796 INSERT INTO query.expression (
806 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
808 COALESCE(NEW.parenthesize, FALSE),
810 COALESCE(NEW.seq_no, 1),
813 COALESCE(NEW.negate, false)
816 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
817 ON UPDATE TO query.expr_xin
819 UPDATE query.expression SET
821 parenthesize = NEW.parenthesize,
822 parent_expr = NEW.parent_expr,
824 left_operand = NEW.left_operand,
825 subquery = NEW.subquery,
830 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
831 ON DELETE TO query.expr_xin
833 DELETE FROM query.expression WHERE id = OLD.id;
835 -- Create updatable view for IS NULL expressions
837 CREATE OR REPLACE VIEW query.expr_xisnull AS
850 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
851 ON INSERT TO query.expr_xisnull
853 INSERT INTO query.expression (
862 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
864 COALESCE(NEW.parenthesize, FALSE),
866 COALESCE(NEW.seq_no, 1),
868 COALESCE(NEW.negate, false)
871 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
872 ON UPDATE TO query.expr_xisnull
874 UPDATE query.expression SET
876 parenthesize = NEW.parenthesize,
877 parent_expr = NEW.parent_expr,
879 left_operand = NEW.left_operand,
884 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
885 ON DELETE TO query.expr_xisnull
887 DELETE FROM query.expression WHERE id = OLD.id;
889 -- Create updatable view for NULL expressions
891 CREATE OR REPLACE VIEW query.expr_xnull AS
903 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
904 ON INSERT TO query.expr_xnull
906 INSERT INTO query.expression (
914 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
916 COALESCE(NEW.parenthesize, FALSE),
918 COALESCE(NEW.seq_no, 1),
919 COALESCE(NEW.negate, false)
922 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
923 ON UPDATE TO query.expr_xnull
925 UPDATE query.expression SET
927 parenthesize = NEW.parenthesize,
928 parent_expr = NEW.parent_expr,
934 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
935 ON DELETE TO query.expr_xnull
937 DELETE FROM query.expression WHERE id = OLD.id;
939 -- Create updatable view for numeric literal expressions
941 CREATE OR REPLACE VIEW query.expr_xnum AS
953 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
954 ON INSERT TO query.expr_xnum
956 INSERT INTO query.expression (
964 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
966 COALESCE(NEW.parenthesize, FALSE),
968 COALESCE(NEW.seq_no, 1),
972 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
973 ON UPDATE TO query.expr_xnum
975 UPDATE query.expression SET
977 parenthesize = NEW.parenthesize,
978 parent_expr = NEW.parent_expr,
980 literal = NEW.literal
984 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
985 ON DELETE TO query.expr_xnum
987 DELETE FROM query.expression WHERE id = OLD.id;
989 -- Create updatable view for operator expressions
991 CREATE OR REPLACE VIEW query.expr_xop AS
1006 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
1007 ON INSERT TO query.expr_xop
1009 INSERT INTO query.expression (
1020 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1022 COALESCE(NEW.parenthesize, FALSE),
1024 COALESCE(NEW.seq_no, 1),
1028 COALESCE(NEW.negate, false)
1031 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
1032 ON UPDATE TO query.expr_xop
1034 UPDATE query.expression SET
1036 parenthesize = NEW.parenthesize,
1037 parent_expr = NEW.parent_expr,
1038 seq_no = NEW.seq_no,
1039 left_operand = NEW.left_operand,
1040 operator = NEW.operator,
1041 right_operand = NEW.right_operand,
1046 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1047 ON DELETE TO query.expr_xop
1049 DELETE FROM query.expression WHERE id = OLD.id;
1051 -- Create updatable view for series expressions,
1052 -- i.e. series of expressions separated by operators
1054 CREATE OR REPLACE VIEW query.expr_xser AS
1067 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1068 ON INSERT TO query.expr_xser
1070 INSERT INTO query.expression (
1079 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1081 COALESCE(NEW.parenthesize, FALSE),
1083 COALESCE(NEW.seq_no, 1),
1085 COALESCE(NEW.negate, false)
1088 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1089 ON UPDATE TO query.expr_xser
1091 UPDATE query.expression SET
1093 parenthesize = NEW.parenthesize,
1094 parent_expr = NEW.parent_expr,
1095 seq_no = NEW.seq_no,
1096 operator = NEW.operator,
1101 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1102 ON DELETE TO query.expr_xser
1104 DELETE FROM query.expression WHERE id = OLD.id;
1106 -- Create updatable view for string literal expressions
1108 CREATE OR REPLACE VIEW query.expr_xstr AS
1120 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1121 ON INSERT TO query.expr_xstr
1123 INSERT INTO query.expression (
1131 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1133 COALESCE(NEW.parenthesize, FALSE),
1135 COALESCE(NEW.seq_no, 1),
1139 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1140 ON UPDATE TO query.expr_xstr
1142 UPDATE query.expression SET
1144 parenthesize = NEW.parenthesize,
1145 parent_expr = NEW.parent_expr,
1146 seq_no = NEW.seq_no,
1147 literal = NEW.literal
1151 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1152 ON DELETE TO query.expr_xstr
1154 DELETE FROM query.expression WHERE id = OLD.id;
1156 -- Create updatable view for subquery expressions
1158 CREATE OR REPLACE VIEW query.expr_xsubq AS
1171 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1172 ON INSERT TO query.expr_xsubq
1174 INSERT INTO query.expression (
1183 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1185 COALESCE(NEW.parenthesize, FALSE),
1187 COALESCE(NEW.seq_no, 1),
1189 COALESCE(NEW.negate, false)
1192 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1193 ON UPDATE TO query.expr_xsubq
1195 UPDATE query.expression SET
1197 parenthesize = NEW.parenthesize,
1198 parent_expr = NEW.parent_expr,
1199 seq_no = NEW.seq_no,
1200 subquery = NEW.subquery,
1205 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1206 ON DELETE TO query.expr_xsubq
1208 DELETE FROM query.expression WHERE id = OLD.id;