Add new expression type 'xser' to represent series expressions,
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 008.schema.query.sql
1 -- Script to create the query schema and the tables therein
2
3 BEGIN;
4
5 DROP SCHEMA IF EXISTS query CASCADE;
6 CREATE SCHEMA query;
7 COMMENT ON SCHEMA actor IS $$
8 /*
9  * Copyright (C) 2009  Equinox Software, Inc. / Georgia Public Library Service
10  * Scott McKellar <scott@esilibrary.com>
11  *
12  * Schema: query
13  *
14  * Contains tables designed to represent user-defined queries for
15  * reports and the like.
16  *
17  * ****
18  *
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.
23  *
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.
28  */
29 $$;
30
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 );
44
45 -- (Foreign keys to be defined later after other tables are created)
46
47 CREATE TABLE query.query_sequence (
48         id              SERIAL            PRIMARY KEY,
49         parent_query    INT               NOT NULL
50                                           REFERENCES query.stored_query
51                                                                           ON DELETE CASCADE
52                                                                           DEFERRABLE INITIALLY DEFERRED,
53         seq_no          INT               NOT NULL,
54         child_query     INT               NOT NULL
55                                           REFERENCES query.stored_query
56                                                                           ON DELETE CASCADE
57                                                                           DEFERRABLE INITIALLY DEFERRED,
58         CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
59 );
60
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 )
68 );
69
70 CREATE TABLE query.subfield (
71         id              SERIAL            PRIMARY KEY,
72         composite_type  INT               NOT NULL
73                                           REFERENCES query.datatype(id)
74                                           ON DELETE CASCADE
75                                           DEFERRABLE INITIALLY DEFERRED,
76         seq_no          INT               NOT NULL
77                                           CONSTRAINT qsf_pos_seq_no
78                                           CHECK( seq_no > 0 ),
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)
83 );
84
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 )
93 );
94
95 CREATE INDEX query_function_sig_name_idx 
96         ON query.function_sig (function_name);
97
98 CREATE TABLE query.function_param_def (
99         id              SERIAL            PRIMARY KEY,
100         function_id     INT               NOT NULL
101                                           REFERENCES query.function_sig( id )
102                                           ON DELETE CASCADE
103                                           DEFERRABLE INITIALLY DEFERRED,
104         seq_no          INT               NOT NULL
105                                           CONSTRAINT qfpd_pos_seq_no CHECK
106                                           ( seq_no > 0 ),
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)
111 );
112
113 CREATE TABLE query.bind_variable (
114         name          TEXT             PRIMARY KEY,
115         type          TEXT             NOT NULL
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
120         label         TEXT             NOT NULL
121 );
122
123 CREATE TABLE query.expression (
124         id            SERIAL        PRIMARY KEY,
125         type          TEXT          NOT NULL CONSTRAINT expression_type CHECK
126                                     ( type IN (
127                                         'xbet',    -- between
128                                                                         'xbind',   -- bind variable
129                                                                         'xbool',   -- boolean
130                                         'xcase',   -- case
131                                                                         'xcast',   -- cast
132                                                                         'xcol',    -- column
133                                                                         'xex',     -- exists
134                                                                         'xfld',    -- field
135                                                                         'xfunc',   -- function
136                                                                         'xin',     -- in
137                                                                         'xisnull', -- is null
138                                         'xnull',   -- null
139                                                                         'xnum',    -- number
140                                                                         'xop',     -- operator
141                                                                         'xser',    -- series
142                                                                         'xstr',    -- string
143                                                 'xsubq'    -- subquery
144                                                                 ) ),
145         parenthesize  BOOL          NOT NULL DEFAULT FALSE,
146         parent_expr   INT           REFERENCES query.expression
147                                     ON DELETE CASCADE
148                                     DEFERRABLE INITIALLY DEFERRED,
149         seq_no        INT           NOT NULL DEFAULT 1,
150         literal       TEXT,
151         table_alias   TEXT,
152         column_name   TEXT,
153         left_operand  INT           REFERENCES query.expression
154                                     DEFERRABLE INITIALLY DEFERRED,
155         operator      TEXT,
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
167 );
168
169 CREATE UNIQUE INDEX query_expr_parent_seq
170         ON query.expression( parent_expr, seq_no )
171         WHERE parent_expr IS NOT NULL;
172
173 -- Due to some circular references, the following foreign key definitions
174 -- had to be deferred until query.expression existed:
175
176 ALTER TABLE query.stored_query
177         ADD FOREIGN KEY ( where_clause )
178         REFERENCES query.expression( id )
179         DEFERRABLE INITIALLY DEFERRED;
180
181 ALTER TABLE query.stored_query
182         ADD FOREIGN KEY ( having_clause )
183         REFERENCES query.expression( id )
184         DEFERRABLE INITIALLY DEFERRED;
185
186 CREATE TABLE query.case_branch (
187         id            SERIAL        PRIMARY KEY,
188         parent_expr   INT           NOT NULL REFERENCES query.expression
189                                     ON DELETE CASCADE
190                                     DEFERRABLE INITIALLY DEFERRED,
191         seq_no        INT           NOT NULL,
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)
197 );
198
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' ) ),
203         table_name       TEXT,
204         class_name       TEXT,
205         subquery         INT           REFERENCES query.stored_query,
206         function_call    INT           REFERENCES query.expression,
207         table_alias      TEXT,
208         parent_relation  INT           REFERENCES query.from_relation
209                                        ON DELETE CASCADE
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' )
215                                        ),
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 )
221             OR
222             ( parent_relation IS NOT NULL AND join_type IS NOT NULL
223               AND on_clause IS NOT NULL )
224         )
225 );
226
227 CREATE UNIQUE INDEX from_parent_seq
228         ON query.from_relation( parent_relation, seq_no )
229         WHERE parent_relation IS NOT NULL;
230
231 -- The following foreign key had to be deferred until
232 -- query.from_relation existed
233
234 ALTER TABLE query.stored_query
235         ADD FOREIGN KEY (from_clause)
236         REFERENCES query.from_relation
237         DEFERRABLE INITIALLY DEFERRED;
238
239 CREATE TABLE query.record_column (
240         id            SERIAL            PRIMARY KEY,
241         from_relation INT               NOT NULL REFERENCES query.from_relation
242                                         ON DELETE CASCADE
243                                         DEFERRABLE INITIALLY DEFERRED,
244         seq_no        INT               NOT NULL,
245         column_name   TEXT              NOT NULL,
246     column_type   INT               NOT NULL REFERENCES query.datatype
247                                     ON DELETE CASCADE
248                                     DEFERRABLE INITIALLY DEFERRED,
249         CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
250 );
251
252 CREATE TABLE query.select_item (
253         id               SERIAL         PRIMARY KEY,
254         stored_query     INT            NOT NULL REFERENCES query.stored_query
255                                         ON DELETE CASCADE
256                                         DEFERRABLE INITIALLY DEFERRED,
257         seq_no           INT            NOT NULL,
258         expression       INT            NOT NULL REFERENCES query.expression
259                                         DEFERRABLE INITIALLY DEFERRED,
260         column_alias     TEXT,
261         grouped_by       BOOL           NOT NULL DEFAULT FALSE,
262         CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
263 );
264
265 CREATE TABLE query.order_by_item (
266         id               SERIAL         PRIMARY KEY,
267         stored_query     INT            NOT NULL REFERENCES query.stored_query
268                                         ON DELETE CASCADE
269                                         DEFERRABLE INITIALLY DEFERRED,
270         seq_no           INT            NOT NULL,
271         expression       INT            NOT NULL REFERENCES query.expression
272                                         ON DELETE CASCADE
273                                         DEFERRABLE INITIALLY DEFERRED,
274         CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
275 );
276
277 -- Create updatable views -------------------------------------------
278
279 -- Create updatable view for BETWEEN expressions
280
281 CREATE OR REPLACE VIEW query.expr_xbet AS
282     SELECT
283                 id,
284                 parenthesize,
285                 parent_expr,
286                 seq_no,
287                 negate
288     FROM
289         query.expression
290     WHERE
291         type = 'xbet';
292
293 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
294     ON INSERT TO query.expr_xbet
295     DO INSTEAD
296     INSERT INTO query.expression (
297                 id,
298                 type,
299                 parenthesize,
300                 parent_expr,
301                 seq_no,
302                 negate
303     ) VALUES (
304         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
305         'xbet',
306         COALESCE(NEW.parenthesize, FALSE),
307         NEW.parent_expr,
308         COALESCE(NEW.seq_no, 1),
309                 NEW.negate
310     );
311
312 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
313     ON UPDATE TO query.expr_xbet
314     DO INSTEAD
315     UPDATE query.expression SET
316         id = NEW.id,
317         parenthesize = NEW.parenthesize,
318         parent_expr = NEW.parent_expr,
319         seq_no = NEW.seq_no,
320                 negate = NEW.negate
321     WHERE
322         id = OLD.id;
323
324 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
325     ON DELETE TO query.expr_xbet
326     DO INSTEAD
327     DELETE FROM query.expression WHERE id = OLD.id;
328
329 -- Create updatable view for bind variable expressions
330
331 CREATE OR REPLACE VIEW query.expr_xbind AS
332     SELECT
333                 id,
334                 parenthesize,
335                 parent_expr,
336                 seq_no,
337                 bind_variable
338     FROM
339         query.expression
340     WHERE
341         type = 'xbind';
342
343 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
344     ON INSERT TO query.expr_xbind
345     DO INSTEAD
346     INSERT INTO query.expression (
347                 id,
348                 type,
349                 parenthesize,
350                 parent_expr,
351                 seq_no,
352                 bind_variable
353     ) VALUES (
354         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
355         'xbind',
356         COALESCE(NEW.parenthesize, FALSE),
357         NEW.parent_expr,
358         COALESCE(NEW.seq_no, 1),
359                 NEW.bind_variable
360     );
361
362 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
363     ON UPDATE TO query.expr_xbind
364     DO INSTEAD
365     UPDATE query.expression SET
366         id = NEW.id,
367         parenthesize = NEW.parenthesize,
368         parent_expr = NEW.parent_expr,
369         seq_no = NEW.seq_no,
370                 bind_variable = NEW.bind_variable
371     WHERE
372         id = OLD.id;
373
374 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
375     ON DELETE TO query.expr_xbind
376     DO INSTEAD
377     DELETE FROM query.expression WHERE id = OLD.id;
378
379 -- Create updatable view for bind variable expressions
380
381 CREATE OR REPLACE VIEW query.expr_xbind AS
382     SELECT
383                 id,
384                 parenthesize,
385                 parent_expr,
386                 seq_no,
387                 bind_variable
388     FROM
389         query.expression
390     WHERE
391         type = 'xbind';
392
393 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
394     ON INSERT TO query.expr_xbind
395     DO INSTEAD
396     INSERT INTO query.expression (
397                 id,
398                 type,
399                 parenthesize,
400                 parent_expr,
401                 seq_no,
402                 bind_variable
403     ) VALUES (
404         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
405         'xbind',
406         COALESCE(NEW.parenthesize, FALSE),
407         NEW.parent_expr,
408         COALESCE(NEW.seq_no, 1),
409                 NEW.bind_variable
410     );
411
412 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
413     ON UPDATE TO query.expr_xbind
414     DO INSTEAD
415     UPDATE query.expression SET
416         id = NEW.id,
417         parenthesize = NEW.parenthesize,
418         parent_expr = NEW.parent_expr,
419         seq_no = NEW.seq_no,
420                 bind_variable = NEW.bind_variable
421     WHERE
422         id = OLD.id;
423
424 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
425     ON DELETE TO query.expr_xbind
426     DO INSTEAD
427     DELETE FROM query.expression WHERE id = OLD.id;
428
429 -- Create updatable view for boolean expressions
430
431 CREATE OR REPLACE VIEW query.expr_xbool AS
432     SELECT
433                 id,
434                 parenthesize,
435                 parent_expr,
436                 seq_no,
437                 literal,
438                 negate
439     FROM
440         query.expression
441     WHERE
442         type = 'xbool';
443
444 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
445     ON INSERT TO query.expr_xbool
446     DO INSTEAD
447     INSERT INTO query.expression (
448                 id,
449                 type,
450                 parenthesize,
451                 parent_expr,
452                 seq_no,
453                 literal,
454                 negate
455     ) VALUES (
456         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
457         'xbool',
458         COALESCE(NEW.parenthesize, FALSE),
459         NEW.parent_expr,
460         COALESCE(NEW.seq_no, 1),
461         NEW.literal,
462                 NEW.negate
463     );
464
465 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
466     ON UPDATE TO query.expr_xbool
467     DO INSTEAD
468     UPDATE query.expression SET
469         id = NEW.id,
470         parenthesize = NEW.parenthesize,
471         parent_expr = NEW.parent_expr,
472         seq_no = NEW.seq_no,
473         literal = NEW.literal,
474                 negate = NEW.negate
475     WHERE
476         id = OLD.id;
477
478 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
479     ON DELETE TO query.expr_xbool
480     DO INSTEAD
481     DELETE FROM query.expression WHERE id = OLD.id;
482
483 -- Create updatable view for CASE expressions
484
485 CREATE OR REPLACE VIEW query.expr_xcase AS
486     SELECT
487                 id,
488                 parenthesize,
489                 parent_expr,
490                 seq_no,
491                 negate
492     FROM
493         query.expression
494     WHERE
495         type = 'xcase';
496
497 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
498     ON INSERT TO query.expr_xcase
499     DO INSTEAD
500     INSERT INTO query.expression (
501                 id,
502                 type,
503                 parenthesize,
504                 parent_expr,
505                 seq_no,
506                 negate
507     ) VALUES (
508         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
509         'xcase',
510         COALESCE(NEW.parenthesize, FALSE),
511         NEW.parent_expr,
512         COALESCE(NEW.seq_no, 1),
513                 NEW.negate
514     );
515
516 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
517     ON UPDATE TO query.expr_xcase
518     DO INSTEAD
519     UPDATE query.expression SET
520         id = NEW.id,
521         parenthesize = NEW.parenthesize,
522         parent_expr = NEW.parent_expr,
523         seq_no = NEW.seq_no,
524                 negate = NEW.negate
525     WHERE
526         id = OLD.id;
527
528 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
529     ON DELETE TO query.expr_xcase
530     DO INSTEAD
531     DELETE FROM query.expression WHERE id = OLD.id;
532
533 -- Create updatable view for cast expressions
534
535 CREATE OR REPLACE VIEW query.expr_xcast AS
536     SELECT
537                 id,
538                 parenthesize,
539                 parent_expr,
540                 seq_no,
541                 left_operand,
542                 cast_type,
543                 negate
544     FROM
545         query.expression
546     WHERE
547         type = 'xcast';
548
549 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
550     ON INSERT TO query.expr_xcast
551     DO INSTEAD
552     INSERT INTO query.expression (
553                 id,
554                 type,
555                 parenthesize,
556                 parent_expr,
557                 seq_no,
558                 left_operand,
559                 cast_type,
560                 negate
561     ) VALUES (
562         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
563         'xcast',
564         COALESCE(NEW.parenthesize, FALSE),
565         NEW.parent_expr,
566         COALESCE(NEW.seq_no, 1),
567                 NEW.left_operand,
568                 NEW.cast_type,
569                 NEW.negate
570     );
571
572 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
573     ON UPDATE TO query.expr_xcast
574     DO INSTEAD
575     UPDATE query.expression SET
576         id = NEW.id,
577         parenthesize = NEW.parenthesize,
578         parent_expr = NEW.parent_expr,
579         seq_no = NEW.seq_no,
580                 left_operand = NEW.left_operand,
581                 cast_type = NEW.cast_type,
582                 negate = NEW.negate
583     WHERE
584         id = OLD.id;
585
586 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
587     ON DELETE TO query.expr_xcast
588     DO INSTEAD
589     DELETE FROM query.expression WHERE id = OLD.id;
590
591 -- Create updatable view for column expressions
592
593 CREATE OR REPLACE VIEW query.expr_xcol AS
594     SELECT
595                 id,
596                 parenthesize,
597                 parent_expr,
598                 seq_no,
599                 table_alias,
600                 column_name,
601                 negate
602     FROM
603         query.expression
604     WHERE
605         type = 'xcol';
606
607 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
608     ON INSERT TO query.expr_xcol
609     DO INSTEAD
610     INSERT INTO query.expression (
611                 id,
612                 type,
613                 parenthesize,
614                 parent_expr,
615                 seq_no,
616                 table_alias,
617                 column_name,
618                 negate
619     ) VALUES (
620         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
621         'xcol',
622         COALESCE(NEW.parenthesize, FALSE),
623         NEW.parent_expr,
624         COALESCE(NEW.seq_no, 1),
625                 NEW.table_alias,
626                 NEW.column_name,
627                 NEW.negate
628     );
629
630 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
631     ON UPDATE TO query.expr_xcol
632     DO INSTEAD
633     UPDATE query.expression SET
634         id = NEW.id,
635         parenthesize = NEW.parenthesize,
636         parent_expr = NEW.parent_expr,
637         seq_no = NEW.seq_no,
638                 table_alias = NEW.table_alias,
639                 column_name = NEW.column_name,
640                 negate = NEW.negate
641     WHERE
642         id = OLD.id;
643
644 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
645     ON DELETE TO query.expr_xcol
646     DO INSTEAD
647     DELETE FROM query.expression WHERE id = OLD.id;
648
649 -- Create updatable view for EXISTS expressions
650
651 CREATE OR REPLACE VIEW query.expr_xex AS
652     SELECT
653                 id,
654                 parenthesize,
655                 parent_expr,
656                 seq_no,
657                 subquery,
658                 negate
659     FROM
660         query.expression
661     WHERE
662         type = 'xex';
663
664 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
665     ON INSERT TO query.expr_xex
666     DO INSTEAD
667     INSERT INTO query.expression (
668                 id,
669                 type,
670                 parenthesize,
671                 parent_expr,
672                 seq_no,
673                 subquery,
674                 negate
675     ) VALUES (
676         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
677         'xex',
678         COALESCE(NEW.parenthesize, FALSE),
679         NEW.parent_expr,
680         COALESCE(NEW.seq_no, 1),
681                 NEW.subquery,
682                 NEW.negate
683     );
684
685 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
686     ON UPDATE TO query.expr_xex
687     DO INSTEAD
688     UPDATE query.expression SET
689         id = NEW.id,
690         parenthesize = NEW.parenthesize,
691         parent_expr = NEW.parent_expr,
692         seq_no = NEW.seq_no,
693                 subquery = NEW.subquery,
694                 negate = NEW.negate
695     WHERE
696         id = OLD.id;
697
698 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
699     ON DELETE TO query.expr_xex
700     DO INSTEAD
701     DELETE FROM query.expression WHERE id = OLD.id;
702
703 -- Create updatable view for field expressions
704
705 CREATE OR REPLACE VIEW query.expr_xfld AS
706     SELECT
707                 id,
708                 parenthesize,
709                 parent_expr,
710                 seq_no,
711                 column_name,
712                 left_operand,
713                 negate
714     FROM
715         query.expression
716     WHERE
717         type = 'xfld';
718
719 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
720     ON INSERT TO query.expr_xfld
721     DO INSTEAD
722     INSERT INTO query.expression (
723                 id,
724                 type,
725                 parenthesize,
726                 parent_expr,
727                 seq_no,
728                 column_name,
729                 left_operand,
730                 negate
731     ) VALUES (
732         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
733         'xfld',
734         COALESCE(NEW.parenthesize, FALSE),
735         NEW.parent_expr,
736         COALESCE(NEW.seq_no, 1),
737                 NEW.column_name,
738                 NEW.left_operand,
739                 NEW.negate
740     );
741
742 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
743     ON UPDATE TO query.expr_xfld
744     DO INSTEAD
745     UPDATE query.expression SET
746         id = NEW.id,
747         parenthesize = NEW.parenthesize,
748         parent_expr = NEW.parent_expr,
749         seq_no = NEW.seq_no,
750                 column_name = NEW.column_name,
751                 left_operand = NEW.left_operand,
752                 negate = NEW.negate
753     WHERE
754         id = OLD.id;
755
756 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
757     ON DELETE TO query.expr_xfld
758     DO INSTEAD
759     DELETE FROM query.expression WHERE id = OLD.id;
760
761 -- Create updatable view for function call expressions
762
763 CREATE OR REPLACE VIEW query.expr_xfunc AS
764     SELECT
765                 id,
766                 parenthesize,
767                 parent_expr,
768                 seq_no,
769                 function_id,
770                 negate
771     FROM
772         query.expression
773     WHERE
774         type = 'xfunc';
775
776 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
777     ON INSERT TO query.expr_xfunc
778     DO INSTEAD
779     INSERT INTO query.expression (
780                 id,
781                 type,
782                 parenthesize,
783                 parent_expr,
784                 seq_no,
785                 function_id,
786                 negate
787     ) VALUES (
788         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
789         'xfunc',
790         COALESCE(NEW.parenthesize, FALSE),
791         NEW.parent_expr,
792         COALESCE(NEW.seq_no, 1),
793                 NEW.function_id,
794                 NEW.negate
795     );
796
797 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
798     ON UPDATE TO query.expr_xfunc
799     DO INSTEAD
800     UPDATE query.expression SET
801         id = NEW.id,
802         parenthesize = NEW.parenthesize,
803         parent_expr = NEW.parent_expr,
804         seq_no = NEW.seq_no,
805                 function_id = NEW.function_id,
806                 negate = NEW.negate
807     WHERE
808         id = OLD.id;
809
810 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
811     ON DELETE TO query.expr_xfunc
812     DO INSTEAD
813     DELETE FROM query.expression WHERE id = OLD.id;
814
815 -- Create updatable view for IN expressions
816
817 CREATE OR REPLACE VIEW query.expr_xin AS
818     SELECT
819                 id,
820                 parenthesize,
821                 parent_expr,
822                 seq_no,
823                 left_operand,
824                 subquery,
825                 negate
826     FROM
827         query.expression
828     WHERE
829         type = 'xin';
830
831 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
832     ON INSERT TO query.expr_xin
833     DO INSTEAD
834     INSERT INTO query.expression (
835                 id,
836                 type,
837                 parenthesize,
838                 parent_expr,
839                 seq_no,
840                 left_operand,
841                 subquery,
842                 negate
843     ) VALUES (
844         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
845         'xin',
846         COALESCE(NEW.parenthesize, FALSE),
847         NEW.parent_expr,
848         COALESCE(NEW.seq_no, 1),
849                 NEW.left_operand,
850                 NEW.subquery,
851                 NEW.negate
852     );
853
854 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
855     ON UPDATE TO query.expr_xin
856     DO INSTEAD
857     UPDATE query.expression SET
858         id = NEW.id,
859         parenthesize = NEW.parenthesize,
860         parent_expr = NEW.parent_expr,
861         seq_no = NEW.seq_no,
862                 left_operand = NEW.left_operand,
863                 subquery = NEW.subquery,
864                 negate = NEW.negate
865     WHERE
866         id = OLD.id;
867
868 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
869     ON DELETE TO query.expr_xin
870     DO INSTEAD
871     DELETE FROM query.expression WHERE id = OLD.id;
872
873 -- Create updatable view for IS NULL expressions
874
875 CREATE OR REPLACE VIEW query.expr_xisnull AS
876     SELECT
877                 id,
878                 parenthesize,
879                 parent_expr,
880                 seq_no,
881                 left_operand,
882                 negate
883     FROM
884         query.expression
885     WHERE
886         type = 'xisnull';
887
888 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
889     ON INSERT TO query.expr_xisnull
890     DO INSTEAD
891     INSERT INTO query.expression (
892                 id,
893                 type,
894                 parenthesize,
895                 parent_expr,
896                 seq_no,
897                 left_operand,
898                 negate
899     ) VALUES (
900         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
901         'xisnull',
902         COALESCE(NEW.parenthesize, FALSE),
903         NEW.parent_expr,
904         COALESCE(NEW.seq_no, 1),
905                 NEW.left_operand,
906                 NEW.negate
907     );
908
909 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
910     ON UPDATE TO query.expr_xisnull
911     DO INSTEAD
912     UPDATE query.expression SET
913         id = NEW.id,
914         parenthesize = NEW.parenthesize,
915         parent_expr = NEW.parent_expr,
916         seq_no = NEW.seq_no,
917                 left_operand = NEW.left_operand,
918                 negate = NEW.negate
919     WHERE
920         id = OLD.id;
921
922 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
923     ON DELETE TO query.expr_xisnull
924     DO INSTEAD
925     DELETE FROM query.expression WHERE id = OLD.id;
926
927 -- Create updatable view for NULL expressions
928
929 CREATE OR REPLACE VIEW query.expr_xnull AS
930     SELECT
931                 id,
932                 parenthesize,
933                 parent_expr,
934                 seq_no,
935                 negate
936     FROM
937         query.expression
938     WHERE
939         type = 'xnull';
940
941 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
942     ON INSERT TO query.expr_xnull
943     DO INSTEAD
944     INSERT INTO query.expression (
945                 id,
946                 type,
947                 parenthesize,
948                 parent_expr,
949                 seq_no,
950                 negate
951     ) VALUES (
952         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
953         'xnull',
954         COALESCE(NEW.parenthesize, FALSE),
955         NEW.parent_expr,
956         COALESCE(NEW.seq_no, 1),
957                 NEW.negate
958     );
959
960 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
961     ON UPDATE TO query.expr_xnull
962     DO INSTEAD
963     UPDATE query.expression SET
964         id = NEW.id,
965         parenthesize = NEW.parenthesize,
966         parent_expr = NEW.parent_expr,
967         seq_no = NEW.seq_no,
968                 negate = NEW.negate
969     WHERE
970         id = OLD.id;
971
972 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
973     ON DELETE TO query.expr_xnull
974     DO INSTEAD
975     DELETE FROM query.expression WHERE id = OLD.id;
976
977 -- Create updatable view for numeric literal expressions
978
979 CREATE OR REPLACE VIEW query.expr_xnum AS
980     SELECT
981                 id,
982                 parenthesize,
983                 parent_expr,
984                 seq_no,
985                 literal
986     FROM
987         query.expression
988     WHERE
989         type = 'xnum';
990
991 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
992     ON INSERT TO query.expr_xnum
993     DO INSTEAD
994     INSERT INTO query.expression (
995                 id,
996                 type,
997                 parenthesize,
998                 parent_expr,
999                 seq_no,
1000                 literal
1001     ) VALUES (
1002         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1003         'xnum',
1004         COALESCE(NEW.parenthesize, FALSE),
1005         NEW.parent_expr,
1006         COALESCE(NEW.seq_no, 1),
1007         NEW.literal
1008     );
1009
1010 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
1011     ON UPDATE TO query.expr_xnum
1012     DO INSTEAD
1013     UPDATE query.expression SET
1014         id = NEW.id,
1015         parenthesize = NEW.parenthesize,
1016         parent_expr = NEW.parent_expr,
1017         seq_no = NEW.seq_no,
1018         literal = NEW.literal
1019     WHERE
1020         id = OLD.id;
1021
1022 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
1023     ON DELETE TO query.expr_xnum
1024     DO INSTEAD
1025     DELETE FROM query.expression WHERE id = OLD.id;
1026
1027 -- Create updatable view for operator expressions
1028
1029 CREATE OR REPLACE VIEW query.expr_xop AS
1030     SELECT
1031                 id,
1032                 parenthesize,
1033                 parent_expr,
1034                 seq_no,
1035                 left_operand,
1036                 operator,
1037                 right_operand,
1038                 negate
1039     FROM
1040         query.expression
1041     WHERE
1042         type = 'xop';
1043
1044 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
1045     ON INSERT TO query.expr_xop
1046     DO INSTEAD
1047     INSERT INTO query.expression (
1048                 id,
1049                 type,
1050                 parenthesize,
1051                 parent_expr,
1052                 seq_no,
1053                 left_operand,
1054                 operator,
1055                 right_operand,
1056                 negate
1057     ) VALUES (
1058         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1059         'xop',
1060         COALESCE(NEW.parenthesize, FALSE),
1061         NEW.parent_expr,
1062         COALESCE(NEW.seq_no, 1),
1063                 NEW.left_operand,
1064                 NEW.operator,
1065                 NEW.right_operand,
1066                 NEW.negate
1067     );
1068
1069 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
1070     ON UPDATE TO query.expr_xop
1071     DO INSTEAD
1072     UPDATE query.expression SET
1073         id = NEW.id,
1074         parenthesize = NEW.parenthesize,
1075         parent_expr = NEW.parent_expr,
1076         seq_no = NEW.seq_no,
1077                 left_operand = NEW.left_operand,
1078                 operator = NEW.operator,
1079                 right_operand = NEW.right_operand,
1080                 negate = NEW.negate
1081     WHERE
1082         id = OLD.id;
1083
1084 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1085     ON DELETE TO query.expr_xop
1086     DO INSTEAD
1087     DELETE FROM query.expression WHERE id = OLD.id;
1088
1089 -- Create updatable view for series expressions,
1090 -- i.e. series of expressions separated by operators
1091
1092 CREATE OR REPLACE VIEW query.expr_xser AS
1093     SELECT
1094                 id,
1095                 parenthesize,
1096                 parent_expr,
1097                 seq_no,
1098                 operator,
1099                 negate
1100     FROM
1101         query.expression
1102     WHERE
1103         type = 'xser';
1104
1105 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1106     ON INSERT TO query.expr_xser
1107     DO INSTEAD
1108     INSERT INTO query.expression (
1109                 id,
1110                 type,
1111                 parenthesize,
1112                 parent_expr,
1113                 seq_no,
1114                 operator,
1115                 negate
1116     ) VALUES (
1117         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1118         'xser',
1119         COALESCE(NEW.parenthesize, FALSE),
1120         NEW.parent_expr,
1121         COALESCE(NEW.seq_no, 1),
1122                 NEW.operator,
1123                 NEW.negate
1124     );
1125
1126 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1127     ON UPDATE TO query.expr_xser
1128     DO INSTEAD
1129     UPDATE query.expression SET
1130         id = NEW.id,
1131         parenthesize = NEW.parenthesize,
1132         parent_expr = NEW.parent_expr,
1133         seq_no = NEW.seq_no,
1134                 operator = NEW.operator,
1135                 negate = NEW.negate
1136     WHERE
1137         id = OLD.id;
1138
1139 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1140     ON DELETE TO query.expr_xser
1141     DO INSTEAD
1142     DELETE FROM query.expression WHERE id = OLD.id;
1143
1144 -- Create updatable view for string literal expressions
1145
1146 CREATE OR REPLACE VIEW query.expr_xstr AS
1147     SELECT
1148         id,
1149         parenthesize,
1150         parent_expr,
1151         seq_no,
1152         literal
1153     FROM
1154         query.expression
1155     WHERE
1156         type = 'xstr';
1157
1158 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1159     ON INSERT TO query.expr_xstr
1160     DO INSTEAD
1161     INSERT INTO query.expression (
1162         id,
1163         type,
1164         parenthesize,
1165         parent_expr,
1166         seq_no,
1167         literal
1168     ) VALUES (
1169         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1170         'xstr',
1171         COALESCE(NEW.parenthesize, FALSE),
1172         NEW.parent_expr,
1173         COALESCE(NEW.seq_no, 1),
1174         NEW.literal
1175     );
1176
1177 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1178     ON UPDATE TO query.expr_xstr
1179     DO INSTEAD
1180     UPDATE query.expression SET
1181         id = NEW.id,
1182         parenthesize = NEW.parenthesize,
1183         parent_expr = NEW.parent_expr,
1184         seq_no = NEW.seq_no,
1185         literal = NEW.literal
1186     WHERE
1187         id = OLD.id;
1188
1189 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1190     ON DELETE TO query.expr_xstr
1191     DO INSTEAD
1192     DELETE FROM query.expression WHERE id = OLD.id;
1193
1194 -- Create updatable view for subquery expressions
1195
1196 CREATE OR REPLACE VIEW query.expr_xsubq AS
1197     SELECT
1198                 id,
1199                 parenthesize,
1200                 parent_expr,
1201                 seq_no,
1202                 subquery,
1203                 negate
1204     FROM
1205         query.expression
1206     WHERE
1207         type = 'xsubq';
1208
1209 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1210     ON INSERT TO query.expr_xsubq
1211     DO INSTEAD
1212     INSERT INTO query.expression (
1213                 id,
1214                 type,
1215                 parenthesize,
1216                 parent_expr,
1217                 seq_no,
1218                 subquery,
1219                 negate
1220     ) VALUES (
1221         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1222         'xsubq',
1223         COALESCE(NEW.parenthesize, FALSE),
1224         NEW.parent_expr,
1225         COALESCE(NEW.seq_no, 1),
1226                 NEW.subquery,
1227                 NEW.negate
1228     );
1229
1230 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1231     ON UPDATE TO query.expr_xsubq
1232     DO INSTEAD
1233     UPDATE query.expression SET
1234         id = NEW.id,
1235         parenthesize = NEW.parenthesize,
1236         parent_expr = NEW.parent_expr,
1237         seq_no = NEW.seq_no,
1238                 subquery = NEW.subquery,
1239                 negate = NEW.negate
1240     WHERE
1241         id = OLD.id;
1242
1243 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1244     ON DELETE TO query.expr_xsubq
1245     DO INSTEAD
1246     DELETE FROM query.expression WHERE id = OLD.id;
1247
1248 COMMIT;