2 * Copyright (C) 2009 Equinox Software, Inc. / Georgia Public Library Service
3 * Scott McKellar <scott@esilibrary.com>
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS query CASCADE;
20 COMMENT ON SCHEMA query IS $$
21 Contains tables designed to represent user-defined queries for
25 CREATE TABLE query.stored_query (
26 id SERIAL PRIMARY KEY,
27 type TEXT NOT NULL CONSTRAINT query_type CHECK
28 ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ),
29 use_all BOOLEAN NOT NULL DEFAULT FALSE,
30 use_distinct BOOLEAN NOT NULL DEFAULT FALSE,
31 from_clause INT, --REFERENCES query.from_clause
32 --DEFERRABLE INITIALLY DEFERRED,
33 where_clause INT, --REFERENCES query.expression
34 --DEFERRABLE INITIALLY DEFERRED,
35 having_clause INT, --REFERENCES query.expression
36 --DEFERRABLE INITIALLY DEFERRED,
37 limit_count INT, --REFERENCES query.expression
38 --DEFERRABLE INITIALLY DEFERRED,
39 offset_count INT --REFERENCES query.expression
40 --DEFERRABLE INITIALLY DEFERRED
43 -- (Foreign keys to be defined later after other tables are created)
45 CREATE TABLE query.query_sequence (
46 id SERIAL PRIMARY KEY,
47 parent_query INT NOT NULL
48 REFERENCES query.stored_query
50 DEFERRABLE INITIALLY DEFERRED,
52 child_query INT NOT NULL
53 REFERENCES query.stored_query
55 DEFERRABLE INITIALLY DEFERRED,
56 CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
59 CREATE TABLE query.datatype (
60 id SERIAL PRIMARY KEY,
61 datatype_name TEXT NOT NULL UNIQUE,
62 is_numeric BOOL NOT NULL DEFAULT FALSE,
63 is_composite BOOL NOT NULL DEFAULT FALSE,
64 CONSTRAINT qdt_comp_not_num CHECK
65 ( is_numeric IS FALSE OR is_composite IS FALSE )
68 -- Leave room to seed with stock datatypes
69 -- before adding customized ones
70 SELECT setval( 'query.datatype_id_seq', 1000 );
72 CREATE TABLE query.subfield (
73 id SERIAL PRIMARY KEY,
74 composite_type INT NOT NULL
75 REFERENCES query.datatype(id)
77 DEFERRABLE INITIALLY DEFERRED,
79 CONSTRAINT qsf_pos_seq_no
81 subfield_type INT NOT NULL
82 REFERENCES query.datatype(id)
83 DEFERRABLE INITIALLY DEFERRED,
84 CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
87 CREATE TABLE query.function_sig (
88 id SERIAL PRIMARY KEY,
89 function_name TEXT NOT NULL,
90 return_type INT REFERENCES query.datatype(id)
91 DEFERRABLE INITIALLY DEFERRED,
92 is_aggregate BOOL NOT NULL DEFAULT FALSE,
93 CONSTRAINT qfd_rtn_or_aggr CHECK
94 ( return_type IS NULL OR is_aggregate = FALSE )
97 CREATE INDEX query_function_sig_name_idx
98 ON query.function_sig (function_name);
100 CREATE TABLE query.function_param_def (
101 id SERIAL PRIMARY KEY,
102 function_id INT NOT NULL
103 REFERENCES query.function_sig( id )
105 DEFERRABLE INITIALLY DEFERRED,
107 CONSTRAINT qfpd_pos_seq_no CHECK
109 datatype INT NOT NULL
110 REFERENCES query.datatype( id )
111 DEFERRABLE INITIALLY DEFERRED,
112 CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
115 CREATE TABLE query.bind_variable (
116 name TEXT PRIMARY KEY,
118 CONSTRAINT bind_variable_type CHECK
119 ( type in ( 'string', 'number', 'string_list', 'number_list' )),
120 description TEXT NOT NULL,
121 default_value TEXT, -- to be encoded in JSON
125 CREATE TABLE query.expression (
126 id SERIAL PRIMARY KEY,
127 type TEXT NOT NULL CONSTRAINT expression_type CHECK
130 'xbind', -- bind variable
138 'xisnull', -- is null
146 parenthesize BOOL NOT NULL DEFAULT FALSE,
147 parent_expr INT REFERENCES query.expression
149 DEFERRABLE INITIALLY DEFERRED,
150 seq_no INT NOT NULL DEFAULT 1,
154 left_operand INT REFERENCES query.expression
155 DEFERRABLE INITIALLY DEFERRED,
157 right_operand INT REFERENCES query.expression
158 DEFERRABLE INITIALLY DEFERRED,
159 function_id INT REFERENCES query.function_sig
160 DEFERRABLE INITIALLY DEFERRED,
161 subquery INT REFERENCES query.stored_query
162 DEFERRABLE INITIALLY DEFERRED,
163 cast_type INT REFERENCES query.datatype
164 DEFERRABLE INITIALLY DEFERRED,
165 negate BOOL NOT NULL DEFAULT FALSE,
166 bind_variable TEXT REFERENCES query.bind_variable
167 DEFERRABLE INITIALLY DEFERRED
170 CREATE UNIQUE INDEX query_expr_parent_seq
171 ON query.expression( parent_expr, seq_no )
172 WHERE parent_expr IS NOT NULL;
174 -- Due to some circular references, the following foreign key definitions
175 -- had to be deferred until query.expression existed:
177 ALTER TABLE query.stored_query
178 ADD FOREIGN KEY ( where_clause )
179 REFERENCES query.expression( id )
180 DEFERRABLE INITIALLY DEFERRED;
182 ALTER TABLE query.stored_query
183 ADD FOREIGN KEY ( having_clause )
184 REFERENCES query.expression( id )
185 DEFERRABLE INITIALLY DEFERRED;
187 ALTER TABLE query.stored_query
188 ADD FOREIGN KEY ( limit_count )
189 REFERENCES query.expression( id )
190 DEFERRABLE INITIALLY DEFERRED;
192 ALTER TABLE query.stored_query
193 ADD FOREIGN KEY ( offset_count )
194 REFERENCES query.expression( id )
195 DEFERRABLE INITIALLY DEFERRED;
197 CREATE TABLE query.case_branch (
198 id SERIAL PRIMARY KEY,
199 parent_expr INT NOT NULL REFERENCES query.expression
201 DEFERRABLE INITIALLY DEFERRED,
203 condition INT REFERENCES query.expression
204 DEFERRABLE INITIALLY DEFERRED,
205 result INT NOT NULL REFERENCES query.expression
206 DEFERRABLE INITIALLY DEFERRED,
207 CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
210 CREATE TABLE query.from_relation (
211 id SERIAL PRIMARY KEY,
212 type TEXT NOT NULL CONSTRAINT relation_type CHECK (
213 type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
216 subquery INT REFERENCES query.stored_query,
217 function_call INT REFERENCES query.expression,
219 parent_relation INT REFERENCES query.from_relation
221 DEFERRABLE INITIALLY DEFERRED,
222 seq_no INT NOT NULL DEFAULT 1,
223 join_type TEXT CONSTRAINT good_join_type CHECK (
224 join_type IS NULL OR join_type IN
225 ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
227 on_clause INT REFERENCES query.expression
228 DEFERRABLE INITIALLY DEFERRED,
229 CONSTRAINT join_or_core CHECK (
230 ( parent_relation IS NULL AND join_type IS NULL
231 AND on_clause IS NULL )
233 ( parent_relation IS NOT NULL AND join_type IS NOT NULL
234 AND on_clause IS NOT NULL )
238 CREATE UNIQUE INDEX from_parent_seq
239 ON query.from_relation( parent_relation, seq_no )
240 WHERE parent_relation IS NOT NULL;
242 -- The following foreign key had to be deferred until
243 -- query.from_relation existed
245 ALTER TABLE query.stored_query
246 ADD FOREIGN KEY (from_clause)
247 REFERENCES query.from_relation
248 DEFERRABLE INITIALLY DEFERRED;
250 CREATE TABLE query.record_column (
251 id SERIAL PRIMARY KEY,
252 from_relation INT NOT NULL REFERENCES query.from_relation
254 DEFERRABLE INITIALLY DEFERRED,
256 column_name TEXT NOT NULL,
257 column_type INT NOT NULL REFERENCES query.datatype
259 DEFERRABLE INITIALLY DEFERRED,
260 CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
263 CREATE TABLE query.select_item (
264 id SERIAL PRIMARY KEY,
265 stored_query INT NOT NULL REFERENCES query.stored_query
267 DEFERRABLE INITIALLY DEFERRED,
269 expression INT NOT NULL REFERENCES query.expression
270 DEFERRABLE INITIALLY DEFERRED,
272 grouped_by BOOL NOT NULL DEFAULT FALSE,
273 CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
276 CREATE TABLE query.order_by_item (
277 id SERIAL PRIMARY KEY,
278 stored_query INT NOT NULL REFERENCES query.stored_query
280 DEFERRABLE INITIALLY DEFERRED,
282 expression INT NOT NULL REFERENCES query.expression
284 DEFERRABLE INITIALLY DEFERRED,
285 CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
288 -- Create updatable views -------------------------------------------
290 -- Create updatable view for BETWEEN expressions
292 CREATE OR REPLACE VIEW query.expr_xbet AS
305 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
306 ON INSERT TO query.expr_xbet
308 INSERT INTO query.expression (
317 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
319 COALESCE(NEW.parenthesize, FALSE),
321 COALESCE(NEW.seq_no, 1),
323 COALESCE(NEW.negate, false)
326 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
327 ON UPDATE TO query.expr_xbet
329 UPDATE query.expression SET
331 parenthesize = NEW.parenthesize,
332 parent_expr = NEW.parent_expr,
334 left_operand = NEW.left_operand,
339 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
340 ON DELETE TO query.expr_xbet
342 DELETE FROM query.expression WHERE id = OLD.id;
344 -- Create updatable view for bind variable expressions
346 CREATE OR REPLACE VIEW query.expr_xbind AS
358 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
359 ON INSERT TO query.expr_xbind
361 INSERT INTO query.expression (
369 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
371 COALESCE(NEW.parenthesize, FALSE),
373 COALESCE(NEW.seq_no, 1),
377 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
378 ON UPDATE TO query.expr_xbind
380 UPDATE query.expression SET
382 parenthesize = NEW.parenthesize,
383 parent_expr = NEW.parent_expr,
385 bind_variable = NEW.bind_variable
389 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
390 ON DELETE TO query.expr_xbind
392 DELETE FROM query.expression WHERE id = OLD.id;
394 -- Create updatable view for boolean expressions
396 CREATE OR REPLACE VIEW query.expr_xbool AS
409 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
410 ON INSERT TO query.expr_xbool
412 INSERT INTO query.expression (
421 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
423 COALESCE(NEW.parenthesize, FALSE),
425 COALESCE(NEW.seq_no, 1),
427 COALESCE(NEW.negate, false)
430 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
431 ON UPDATE TO query.expr_xbool
433 UPDATE query.expression SET
435 parenthesize = NEW.parenthesize,
436 parent_expr = NEW.parent_expr,
438 literal = NEW.literal,
443 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
444 ON DELETE TO query.expr_xbool
446 DELETE FROM query.expression WHERE id = OLD.id;
448 -- Create updatable view for CASE expressions
450 CREATE OR REPLACE VIEW query.expr_xcase AS
463 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
464 ON INSERT TO query.expr_xcase
466 INSERT INTO query.expression (
475 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
477 COALESCE(NEW.parenthesize, FALSE),
479 COALESCE(NEW.seq_no, 1),
481 COALESCE(NEW.negate, false)
484 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
485 ON UPDATE TO query.expr_xcase
487 UPDATE query.expression SET
489 parenthesize = NEW.parenthesize,
490 parent_expr = NEW.parent_expr,
492 left_operand = NEW.left_operand,
497 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
498 ON DELETE TO query.expr_xcase
500 DELETE FROM query.expression WHERE id = OLD.id;
502 -- Create updatable view for cast expressions
504 CREATE OR REPLACE VIEW query.expr_xcast AS
518 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
519 ON INSERT TO query.expr_xcast
521 INSERT INTO query.expression (
531 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
533 COALESCE(NEW.parenthesize, FALSE),
535 COALESCE(NEW.seq_no, 1),
538 COALESCE(NEW.negate, false)
541 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
542 ON UPDATE TO query.expr_xcast
544 UPDATE query.expression SET
546 parenthesize = NEW.parenthesize,
547 parent_expr = NEW.parent_expr,
549 left_operand = NEW.left_operand,
550 cast_type = NEW.cast_type,
555 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
556 ON DELETE TO query.expr_xcast
558 DELETE FROM query.expression WHERE id = OLD.id;
560 -- Create updatable view for column expressions
562 CREATE OR REPLACE VIEW query.expr_xcol AS
576 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
577 ON INSERT TO query.expr_xcol
579 INSERT INTO query.expression (
589 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
591 COALESCE(NEW.parenthesize, FALSE),
593 COALESCE(NEW.seq_no, 1),
596 COALESCE(NEW.negate, false)
599 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
600 ON UPDATE TO query.expr_xcol
602 UPDATE query.expression SET
604 parenthesize = NEW.parenthesize,
605 parent_expr = NEW.parent_expr,
607 table_alias = NEW.table_alias,
608 column_name = NEW.column_name,
613 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
614 ON DELETE TO query.expr_xcol
616 DELETE FROM query.expression WHERE id = OLD.id;
618 -- Create updatable view for EXISTS expressions
620 CREATE OR REPLACE VIEW query.expr_xex AS
633 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
634 ON INSERT TO query.expr_xex
636 INSERT INTO query.expression (
645 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
647 COALESCE(NEW.parenthesize, FALSE),
649 COALESCE(NEW.seq_no, 1),
651 COALESCE(NEW.negate, false)
654 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
655 ON UPDATE TO query.expr_xex
657 UPDATE query.expression SET
659 parenthesize = NEW.parenthesize,
660 parent_expr = NEW.parent_expr,
662 subquery = NEW.subquery,
667 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
668 ON DELETE TO query.expr_xex
670 DELETE FROM query.expression WHERE id = OLD.id;
672 -- Create updatable view for function call expressions
674 CREATE OR REPLACE VIEW query.expr_xfunc AS
688 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
689 ON INSERT TO query.expr_xfunc
691 INSERT INTO query.expression (
701 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
703 COALESCE(NEW.parenthesize, FALSE),
705 COALESCE(NEW.seq_no, 1),
708 COALESCE(NEW.negate, false)
711 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
712 ON UPDATE TO query.expr_xfunc
714 UPDATE query.expression SET
716 parenthesize = NEW.parenthesize,
717 parent_expr = NEW.parent_expr,
719 column_name = NEW.column_name,
720 function_id = NEW.function_id,
725 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
726 ON DELETE TO query.expr_xfunc
728 DELETE FROM query.expression WHERE id = OLD.id;
730 -- Create updatable view for IN expressions
732 CREATE OR REPLACE VIEW query.expr_xin AS
746 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
747 ON INSERT TO query.expr_xin
749 INSERT INTO query.expression (
759 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
761 COALESCE(NEW.parenthesize, FALSE),
763 COALESCE(NEW.seq_no, 1),
766 COALESCE(NEW.negate, false)
769 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
770 ON UPDATE TO query.expr_xin
772 UPDATE query.expression SET
774 parenthesize = NEW.parenthesize,
775 parent_expr = NEW.parent_expr,
777 left_operand = NEW.left_operand,
778 subquery = NEW.subquery,
783 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
784 ON DELETE TO query.expr_xin
786 DELETE FROM query.expression WHERE id = OLD.id;
788 -- Create updatable view for IS NULL expressions
790 CREATE OR REPLACE VIEW query.expr_xisnull AS
803 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
804 ON INSERT TO query.expr_xisnull
806 INSERT INTO query.expression (
815 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
817 COALESCE(NEW.parenthesize, FALSE),
819 COALESCE(NEW.seq_no, 1),
821 COALESCE(NEW.negate, false)
824 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
825 ON UPDATE TO query.expr_xisnull
827 UPDATE query.expression SET
829 parenthesize = NEW.parenthesize,
830 parent_expr = NEW.parent_expr,
832 left_operand = NEW.left_operand,
837 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
838 ON DELETE TO query.expr_xisnull
840 DELETE FROM query.expression WHERE id = OLD.id;
842 -- Create updatable view for NULL expressions
844 CREATE OR REPLACE VIEW query.expr_xnull AS
856 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
857 ON INSERT TO query.expr_xnull
859 INSERT INTO query.expression (
867 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
869 COALESCE(NEW.parenthesize, FALSE),
871 COALESCE(NEW.seq_no, 1),
872 COALESCE(NEW.negate, false)
875 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
876 ON UPDATE TO query.expr_xnull
878 UPDATE query.expression SET
880 parenthesize = NEW.parenthesize,
881 parent_expr = NEW.parent_expr,
887 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
888 ON DELETE TO query.expr_xnull
890 DELETE FROM query.expression WHERE id = OLD.id;
892 -- Create updatable view for numeric literal expressions
894 CREATE OR REPLACE VIEW query.expr_xnum AS
906 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
907 ON INSERT TO query.expr_xnum
909 INSERT INTO query.expression (
917 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
919 COALESCE(NEW.parenthesize, FALSE),
921 COALESCE(NEW.seq_no, 1),
925 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
926 ON UPDATE TO query.expr_xnum
928 UPDATE query.expression SET
930 parenthesize = NEW.parenthesize,
931 parent_expr = NEW.parent_expr,
933 literal = NEW.literal
937 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
938 ON DELETE TO query.expr_xnum
940 DELETE FROM query.expression WHERE id = OLD.id;
942 -- Create updatable view for operator expressions
944 CREATE OR REPLACE VIEW query.expr_xop AS
959 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
960 ON INSERT TO query.expr_xop
962 INSERT INTO query.expression (
973 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
975 COALESCE(NEW.parenthesize, FALSE),
977 COALESCE(NEW.seq_no, 1),
981 COALESCE(NEW.negate, false)
984 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
985 ON UPDATE TO query.expr_xop
987 UPDATE query.expression SET
989 parenthesize = NEW.parenthesize,
990 parent_expr = NEW.parent_expr,
992 left_operand = NEW.left_operand,
993 operator = NEW.operator,
994 right_operand = NEW.right_operand,
999 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1000 ON DELETE TO query.expr_xop
1002 DELETE FROM query.expression WHERE id = OLD.id;
1004 -- Create updatable view for series expressions,
1005 -- i.e. series of expressions separated by operators
1007 CREATE OR REPLACE VIEW query.expr_xser AS
1020 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1021 ON INSERT TO query.expr_xser
1023 INSERT INTO query.expression (
1032 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1034 COALESCE(NEW.parenthesize, FALSE),
1036 COALESCE(NEW.seq_no, 1),
1038 COALESCE(NEW.negate, false)
1041 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1042 ON UPDATE TO query.expr_xser
1044 UPDATE query.expression SET
1046 parenthesize = NEW.parenthesize,
1047 parent_expr = NEW.parent_expr,
1048 seq_no = NEW.seq_no,
1049 operator = NEW.operator,
1054 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1055 ON DELETE TO query.expr_xser
1057 DELETE FROM query.expression WHERE id = OLD.id;
1059 -- Create updatable view for string literal expressions
1061 CREATE OR REPLACE VIEW query.expr_xstr AS
1073 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1074 ON INSERT TO query.expr_xstr
1076 INSERT INTO query.expression (
1084 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1086 COALESCE(NEW.parenthesize, FALSE),
1088 COALESCE(NEW.seq_no, 1),
1092 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1093 ON UPDATE TO query.expr_xstr
1095 UPDATE query.expression SET
1097 parenthesize = NEW.parenthesize,
1098 parent_expr = NEW.parent_expr,
1099 seq_no = NEW.seq_no,
1100 literal = NEW.literal
1104 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1105 ON DELETE TO query.expr_xstr
1107 DELETE FROM query.expression WHERE id = OLD.id;
1109 -- Create updatable view for subquery expressions
1111 CREATE OR REPLACE VIEW query.expr_xsubq AS
1124 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1125 ON INSERT TO query.expr_xsubq
1127 INSERT INTO query.expression (
1136 COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1138 COALESCE(NEW.parenthesize, FALSE),
1140 COALESCE(NEW.seq_no, 1),
1142 COALESCE(NEW.negate, false)
1145 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1146 ON UPDATE TO query.expr_xsubq
1148 UPDATE query.expression SET
1150 parenthesize = NEW.parenthesize,
1151 parent_expr = NEW.parent_expr,
1152 seq_no = NEW.seq_no,
1153 subquery = NEW.subquery,
1158 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1159 ON DELETE TO query.expr_xsubq
1161 DELETE FROM query.expression WHERE id = OLD.id;