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
144 parenthesize BOOL NOT NULL DEFAULT FALSE,
145 parent_expr INT REFERENCES query.expression
147 DEFERRABLE INITIALLY DEFERRED,
148 seq_no INT NOT NULL DEFAULT 1,
152 left_operand INT REFERENCES query.expression
153 DEFERRABLE INITIALLY DEFERRED,
155 right_operand INT REFERENCES query.expression
156 DEFERRABLE INITIALLY DEFERRED,
157 function_id INT REFERENCES query.function_sig
158 DEFERRABLE INITIALLY DEFERRED,
159 subquery INT REFERENCES query.stored_query
160 DEFERRABLE INITIALLY DEFERRED,
161 cast_type INT REFERENCES query.datatype
162 DEFERRABLE INITIALLY DEFERRED,
163 negate BOOL NOT NULL DEFAULT FALSE,
164 bind_variable TEXT REFERENCES query.bind_variable
165 DEFERRABLE INITIALLY DEFERRED
168 CREATE UNIQUE INDEX query_expr_parent_seq
169 ON query.expression( parent_expr, seq_no )
170 WHERE parent_expr IS NOT NULL;
172 -- Due to some circular references, the following foreign key definitions
173 -- had to be deferred until query.expression existed:
175 ALTER TABLE query.stored_query
176 ADD FOREIGN KEY ( where_clause )
177 REFERENCES query.expression( id )
178 DEFERRABLE INITIALLY DEFERRED;
180 ALTER TABLE query.stored_query
181 ADD FOREIGN KEY ( having_clause )
182 REFERENCES query.expression( id )
183 DEFERRABLE INITIALLY DEFERRED;
185 CREATE TABLE query.case_branch (
186 id SERIAL PRIMARY KEY,
187 parent_expr INT NOT NULL REFERENCES query.expression
189 DEFERRABLE INITIALLY DEFERRED,
191 condition INT REFERENCES query.expression
192 DEFERRABLE INITIALLY DEFERRED,
193 result INT NOT NULL REFERENCES query.expression
194 DEFERRABLE INITIALLY DEFERRED,
195 CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
198 CREATE TABLE query.from_relation (
199 id SERIAL PRIMARY KEY,
200 type TEXT NOT NULL CONSTRAINT relation_type CHECK (
201 type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
204 subquery INT REFERENCES query.stored_query,
205 function_call INT REFERENCES query.expression,
207 parent_relation INT REFERENCES query.from_relation
209 DEFERRABLE INITIALLY DEFERRED,
210 seq_no INT NOT NULL DEFAULT 1,
211 join_type TEXT CONSTRAINT good_join_type CHECK (
212 join_type IS NULL OR join_type IN
213 ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
215 on_clause INT REFERENCES query.expression
216 DEFERRABLE INITIALLY DEFERRED,
217 CONSTRAINT join_or_core CHECK (
218 ( parent_relation IS NULL AND join_type IS NULL
219 AND on_clause IS NULL )
221 ( parent_relation IS NOT NULL AND join_type IS NOT NULL
222 AND on_clause IS NOT NULL )
226 CREATE UNIQUE INDEX from_parent_seq
227 ON query.from_relation( parent_relation, seq_no )
228 WHERE parent_relation IS NOT NULL;
230 -- The following foreign key had to be deferred until
231 -- query.from_relation existed
233 ALTER TABLE query.stored_query
234 ADD FOREIGN KEY (from_clause)
235 REFERENCES query.from_relation
236 DEFERRABLE INITIALLY DEFERRED;
238 CREATE TABLE query.record_column (
239 id SERIAL PRIMARY KEY,
240 from_relation INT NOT NULL REFERENCES query.from_relation
242 DEFERRABLE INITIALLY DEFERRED,
244 column_name TEXT NOT NULL,
245 column_type INT NOT NULL REFERENCES query.datatype
247 DEFERRABLE INITIALLY DEFERRED,
248 CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
251 CREATE TABLE query.select_item (
252 id SERIAL PRIMARY KEY,
253 stored_query INT NOT NULL REFERENCES query.stored_query
255 DEFERRABLE INITIALLY DEFERRED,
257 expression INT NOT NULL REFERENCES query.expression
258 DEFERRABLE INITIALLY DEFERRED,
260 grouped_by BOOL NOT NULL DEFAULT FALSE,
261 CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
264 CREATE TABLE query.order_by_item (
265 id SERIAL PRIMARY KEY,
266 stored_query INT NOT NULL REFERENCES query.stored_query
268 DEFERRABLE INITIALLY DEFERRED,
270 expression INT NOT NULL REFERENCES query.expression
272 DEFERRABLE INITIALLY DEFERRED,
273 CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
276 -- Create updatable views -------------------------------------------
278 -- Create updatable view for BETWEEN expressions
280 CREATE OR REPLACE VIEW query.expr_xbet AS
292 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
293 ON INSERT TO query.expr_xbet
295 INSERT INTO query.expression (
303 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
305 COALESCE(NEW.parenthesize, FALSE),
307 COALESCE(NEW.seq_no, 1),
311 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
312 ON UPDATE TO query.expr_xbet
314 UPDATE query.expression SET
316 parenthesize = NEW.parenthesize,
317 parent_expr = NEW.parent_expr,
323 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
324 ON DELETE TO query.expr_xbet
326 DELETE FROM query.expression WHERE id = OLD.id;
328 -- Create updatable view for bind variable expressions
330 CREATE OR REPLACE VIEW query.expr_xbind AS
342 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
343 ON INSERT TO query.expr_xbind
345 INSERT INTO query.expression (
353 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
355 COALESCE(NEW.parenthesize, FALSE),
357 COALESCE(NEW.seq_no, 1),
361 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
362 ON UPDATE TO query.expr_xbind
364 UPDATE query.expression SET
366 parenthesize = NEW.parenthesize,
367 parent_expr = NEW.parent_expr,
369 bind_variable = NEW.bind_variable
373 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
374 ON DELETE TO query.expr_xbind
376 DELETE FROM query.expression WHERE id = OLD.id;
378 -- Create updatable view for bind variable expressions
380 CREATE OR REPLACE VIEW query.expr_xbind AS
392 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
393 ON INSERT TO query.expr_xbind
395 INSERT INTO query.expression (
403 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
405 COALESCE(NEW.parenthesize, FALSE),
407 COALESCE(NEW.seq_no, 1),
411 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
412 ON UPDATE TO query.expr_xbind
414 UPDATE query.expression SET
416 parenthesize = NEW.parenthesize,
417 parent_expr = NEW.parent_expr,
419 bind_variable = NEW.bind_variable
423 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
424 ON DELETE TO query.expr_xbind
426 DELETE FROM query.expression WHERE id = OLD.id;
428 -- Create updatable view for boolean expressions
430 CREATE OR REPLACE VIEW query.expr_xbool AS
443 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
444 ON INSERT TO query.expr_xbool
446 INSERT INTO query.expression (
455 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
457 COALESCE(NEW.parenthesize, FALSE),
459 COALESCE(NEW.seq_no, 1),
464 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
465 ON UPDATE TO query.expr_xbool
467 UPDATE query.expression SET
469 parenthesize = NEW.parenthesize,
470 parent_expr = NEW.parent_expr,
472 literal = NEW.literal,
477 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
478 ON DELETE TO query.expr_xbool
480 DELETE FROM query.expression WHERE id = OLD.id;
482 -- Create updatable view for CASE expressions
484 CREATE OR REPLACE VIEW query.expr_xcase AS
496 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
497 ON INSERT TO query.expr_xcase
499 INSERT INTO query.expression (
507 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
509 COALESCE(NEW.parenthesize, FALSE),
511 COALESCE(NEW.seq_no, 1),
515 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
516 ON UPDATE TO query.expr_xcase
518 UPDATE query.expression SET
520 parenthesize = NEW.parenthesize,
521 parent_expr = NEW.parent_expr,
527 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
528 ON DELETE TO query.expr_xcase
530 DELETE FROM query.expression WHERE id = OLD.id;
532 -- Create updatable view for cast expressions
534 CREATE OR REPLACE VIEW query.expr_xcast AS
548 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
549 ON INSERT TO query.expr_xcast
551 INSERT INTO query.expression (
561 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
563 COALESCE(NEW.parenthesize, FALSE),
565 COALESCE(NEW.seq_no, 1),
571 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
572 ON UPDATE TO query.expr_xcast
574 UPDATE query.expression SET
576 parenthesize = NEW.parenthesize,
577 parent_expr = NEW.parent_expr,
579 left_operand = NEW.left_operand,
580 cast_type = NEW.cast_type,
585 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
586 ON DELETE TO query.expr_xcast
588 DELETE FROM query.expression WHERE id = OLD.id;
590 -- Create updatable view for column expressions
592 CREATE OR REPLACE VIEW query.expr_xcol AS
606 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
607 ON INSERT TO query.expr_xcol
609 INSERT INTO query.expression (
619 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
621 COALESCE(NEW.parenthesize, FALSE),
623 COALESCE(NEW.seq_no, 1),
629 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
630 ON UPDATE TO query.expr_xcol
632 UPDATE query.expression SET
634 parenthesize = NEW.parenthesize,
635 parent_expr = NEW.parent_expr,
637 table_alias = NEW.table_alias,
638 column_name = NEW.column_name,
643 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
644 ON DELETE TO query.expr_xcol
646 DELETE FROM query.expression WHERE id = OLD.id;
648 -- Create updatable view for EXISTS expressions
650 CREATE OR REPLACE VIEW query.expr_xex AS
663 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
664 ON INSERT TO query.expr_xex
666 INSERT INTO query.expression (
675 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
677 COALESCE(NEW.parenthesize, FALSE),
679 COALESCE(NEW.seq_no, 1),
684 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
685 ON UPDATE TO query.expr_xex
687 UPDATE query.expression SET
689 parenthesize = NEW.parenthesize,
690 parent_expr = NEW.parent_expr,
692 subquery = NEW.subquery,
697 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
698 ON DELETE TO query.expr_xex
700 DELETE FROM query.expression WHERE id = OLD.id;
702 -- Create updatable view for field expressions
704 CREATE OR REPLACE VIEW query.expr_xfld AS
718 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
719 ON INSERT TO query.expr_xfld
721 INSERT INTO query.expression (
731 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
733 COALESCE(NEW.parenthesize, FALSE),
735 COALESCE(NEW.seq_no, 1),
741 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
742 ON UPDATE TO query.expr_xfld
744 UPDATE query.expression SET
746 parenthesize = NEW.parenthesize,
747 parent_expr = NEW.parent_expr,
749 column_name = NEW.column_name,
750 left_operand = NEW.left_operand,
755 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
756 ON DELETE TO query.expr_xfld
758 DELETE FROM query.expression WHERE id = OLD.id;
760 -- Create updatable view for function call expressions
762 CREATE OR REPLACE VIEW query.expr_xfunc AS
775 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
776 ON INSERT TO query.expr_xfunc
778 INSERT INTO query.expression (
787 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
789 COALESCE(NEW.parenthesize, FALSE),
791 COALESCE(NEW.seq_no, 1),
796 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
797 ON UPDATE TO query.expr_xfunc
799 UPDATE query.expression SET
801 parenthesize = NEW.parenthesize,
802 parent_expr = NEW.parent_expr,
804 function_id = NEW.function_id,
809 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
810 ON DELETE TO query.expr_xfunc
812 DELETE FROM query.expression WHERE id = OLD.id;
814 -- Create updatable view for IN expressions
816 CREATE OR REPLACE VIEW query.expr_xin AS
830 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
831 ON INSERT TO query.expr_xin
833 INSERT INTO query.expression (
843 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
845 COALESCE(NEW.parenthesize, FALSE),
847 COALESCE(NEW.seq_no, 1),
853 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
854 ON UPDATE TO query.expr_xin
856 UPDATE query.expression SET
858 parenthesize = NEW.parenthesize,
859 parent_expr = NEW.parent_expr,
861 left_operand = NEW.left_operand,
862 subquery = NEW.subquery,
867 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
868 ON DELETE TO query.expr_xin
870 DELETE FROM query.expression WHERE id = OLD.id;
872 -- Create updatable view for IS NULL expressions
874 CREATE OR REPLACE VIEW query.expr_xisnull AS
887 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
888 ON INSERT TO query.expr_xisnull
890 INSERT INTO query.expression (
899 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
901 COALESCE(NEW.parenthesize, FALSE),
903 COALESCE(NEW.seq_no, 1),
908 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
909 ON UPDATE TO query.expr_xisnull
911 UPDATE query.expression SET
913 parenthesize = NEW.parenthesize,
914 parent_expr = NEW.parent_expr,
916 left_operand = NEW.left_operand,
921 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
922 ON DELETE TO query.expr_xisnull
924 DELETE FROM query.expression WHERE id = OLD.id;
926 -- Create updatable view for NULL expressions
928 CREATE OR REPLACE VIEW query.expr_xnull AS
940 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
941 ON INSERT TO query.expr_xnull
943 INSERT INTO query.expression (
951 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
953 COALESCE(NEW.parenthesize, FALSE),
955 COALESCE(NEW.seq_no, 1),
959 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
960 ON UPDATE TO query.expr_xnull
962 UPDATE query.expression SET
964 parenthesize = NEW.parenthesize,
965 parent_expr = NEW.parent_expr,
971 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
972 ON DELETE TO query.expr_xnull
974 DELETE FROM query.expression WHERE id = OLD.id;
976 -- Create updatable view for numeric literal expressions
978 CREATE OR REPLACE VIEW query.expr_xnum AS
990 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
991 ON INSERT TO query.expr_xnum
993 INSERT INTO query.expression (
1001 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1003 COALESCE(NEW.parenthesize, FALSE),
1005 COALESCE(NEW.seq_no, 1),
1009 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
1010 ON UPDATE TO query.expr_xnum
1012 UPDATE query.expression SET
1014 parenthesize = NEW.parenthesize,
1015 parent_expr = NEW.parent_expr,
1016 seq_no = NEW.seq_no,
1017 literal = NEW.literal
1021 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
1022 ON DELETE TO query.expr_xnum
1024 DELETE FROM query.expression WHERE id = OLD.id;
1026 -- Create updatable view for operator expressions
1028 CREATE OR REPLACE VIEW query.expr_xop AS
1043 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
1044 ON INSERT TO query.expr_xop
1046 INSERT INTO query.expression (
1057 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1059 COALESCE(NEW.parenthesize, FALSE),
1061 COALESCE(NEW.seq_no, 1),
1068 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
1069 ON UPDATE TO query.expr_xop
1071 UPDATE query.expression SET
1073 parenthesize = NEW.parenthesize,
1074 parent_expr = NEW.parent_expr,
1075 seq_no = NEW.seq_no,
1076 left_operand = NEW.left_operand,
1077 operator = NEW.operator,
1078 right_operand = NEW.right_operand,
1083 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1084 ON DELETE TO query.expr_xop
1086 DELETE FROM query.expression WHERE id = OLD.id;
1088 -- Create updatable view for string literal expressions
1090 CREATE OR REPLACE VIEW query.expr_xstr AS
1102 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1103 ON INSERT TO query.expr_xstr
1105 INSERT INTO query.expression (
1113 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1115 COALESCE(NEW.parenthesize, FALSE),
1117 COALESCE(NEW.seq_no, 1),
1121 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1122 ON UPDATE TO query.expr_xstr
1124 UPDATE query.expression SET
1126 parenthesize = NEW.parenthesize,
1127 parent_expr = NEW.parent_expr,
1128 seq_no = NEW.seq_no,
1129 literal = NEW.literal
1133 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1134 ON DELETE TO query.expr_xstr
1136 DELETE FROM query.expression WHERE id = OLD.id;
1138 -- Create updatable view for subquery expressions
1140 CREATE OR REPLACE VIEW query.expr_xsubq AS
1153 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1154 ON INSERT TO query.expr_xsubq
1156 INSERT INTO query.expression (
1165 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1167 COALESCE(NEW.parenthesize, FALSE),
1169 COALESCE(NEW.seq_no, 1),
1174 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1175 ON UPDATE TO query.expr_xsubq
1177 UPDATE query.expression SET
1179 parenthesize = NEW.parenthesize,
1180 parent_expr = NEW.parent_expr,
1181 seq_no = NEW.seq_no,
1182 subquery = NEW.subquery,
1187 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1188 ON DELETE TO query.expr_xsubq
1190 DELETE FROM query.expression WHERE id = OLD.id;