f3a19affc5ef63505288302fb5b7b99ac4d3ed1d
[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                 left_operand,
288                 negate
289     FROM
290         query.expression
291     WHERE
292         type = 'xbet';
293
294 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
295     ON INSERT TO query.expr_xbet
296     DO INSTEAD
297     INSERT INTO query.expression (
298                 id,
299                 type,
300                 parenthesize,
301                 parent_expr,
302                 seq_no,
303                 left_operand,
304                 negate
305     ) VALUES (
306         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
307         'xbet',
308         COALESCE(NEW.parenthesize, FALSE),
309         NEW.parent_expr,
310         COALESCE(NEW.seq_no, 1),
311                 NEW.left_operand,
312                 COALESCE(NEW.negate, false)
313     );
314
315 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
316     ON UPDATE TO query.expr_xbet
317     DO INSTEAD
318     UPDATE query.expression SET
319         id = NEW.id,
320         parenthesize = NEW.parenthesize,
321         parent_expr = NEW.parent_expr,
322         seq_no = NEW.seq_no,
323                 left_operand = NEW.left_operand,
324                 negate = NEW.negate
325     WHERE
326         id = OLD.id;
327
328 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
329     ON DELETE TO query.expr_xbet
330     DO INSTEAD
331     DELETE FROM query.expression WHERE id = OLD.id;
332
333 -- Create updatable view for bind variable expressions
334
335 CREATE OR REPLACE VIEW query.expr_xbind AS
336     SELECT
337                 id,
338                 parenthesize,
339                 parent_expr,
340                 seq_no,
341                 bind_variable
342     FROM
343         query.expression
344     WHERE
345         type = 'xbind';
346
347 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
348     ON INSERT TO query.expr_xbind
349     DO INSTEAD
350     INSERT INTO query.expression (
351                 id,
352                 type,
353                 parenthesize,
354                 parent_expr,
355                 seq_no,
356                 bind_variable
357     ) VALUES (
358         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
359         'xbind',
360         COALESCE(NEW.parenthesize, FALSE),
361         NEW.parent_expr,
362         COALESCE(NEW.seq_no, 1),
363                 NEW.bind_variable
364     );
365
366 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
367     ON UPDATE TO query.expr_xbind
368     DO INSTEAD
369     UPDATE query.expression SET
370         id = NEW.id,
371         parenthesize = NEW.parenthesize,
372         parent_expr = NEW.parent_expr,
373         seq_no = NEW.seq_no,
374                 bind_variable = NEW.bind_variable
375     WHERE
376         id = OLD.id;
377
378 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
379     ON DELETE TO query.expr_xbind
380     DO INSTEAD
381     DELETE FROM query.expression WHERE id = OLD.id;
382
383 -- Create updatable view for boolean expressions
384
385 CREATE OR REPLACE VIEW query.expr_xbool AS
386     SELECT
387                 id,
388                 parenthesize,
389                 parent_expr,
390                 seq_no,
391                 literal,
392                 negate
393     FROM
394         query.expression
395     WHERE
396         type = 'xbool';
397
398 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
399     ON INSERT TO query.expr_xbool
400     DO INSTEAD
401     INSERT INTO query.expression (
402                 id,
403                 type,
404                 parenthesize,
405                 parent_expr,
406                 seq_no,
407                 literal,
408                 negate
409     ) VALUES (
410         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
411         'xbool',
412         COALESCE(NEW.parenthesize, FALSE),
413         NEW.parent_expr,
414         COALESCE(NEW.seq_no, 1),
415         NEW.literal,
416                 COALESCE(NEW.negate, false)
417     );
418
419 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
420     ON UPDATE TO query.expr_xbool
421     DO INSTEAD
422     UPDATE query.expression SET
423         id = NEW.id,
424         parenthesize = NEW.parenthesize,
425         parent_expr = NEW.parent_expr,
426         seq_no = NEW.seq_no,
427         literal = NEW.literal,
428                 negate = NEW.negate
429     WHERE
430         id = OLD.id;
431
432 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
433     ON DELETE TO query.expr_xbool
434     DO INSTEAD
435     DELETE FROM query.expression WHERE id = OLD.id;
436
437 -- Create updatable view for CASE expressions
438
439 CREATE OR REPLACE VIEW query.expr_xcase AS
440     SELECT
441                 id,
442                 parenthesize,
443                 parent_expr,
444                 seq_no,
445                 negate
446     FROM
447         query.expression
448     WHERE
449         type = 'xcase';
450
451 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
452     ON INSERT TO query.expr_xcase
453     DO INSTEAD
454     INSERT INTO query.expression (
455                 id,
456                 type,
457                 parenthesize,
458                 parent_expr,
459                 seq_no,
460                 negate
461     ) VALUES (
462         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
463         'xcase',
464         COALESCE(NEW.parenthesize, FALSE),
465         NEW.parent_expr,
466         COALESCE(NEW.seq_no, 1),
467                 COALESCE(NEW.negate, false)
468     );
469
470 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
471     ON UPDATE TO query.expr_xcase
472     DO INSTEAD
473     UPDATE query.expression SET
474         id = NEW.id,
475         parenthesize = NEW.parenthesize,
476         parent_expr = NEW.parent_expr,
477         seq_no = NEW.seq_no,
478                 negate = NEW.negate
479     WHERE
480         id = OLD.id;
481
482 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
483     ON DELETE TO query.expr_xcase
484     DO INSTEAD
485     DELETE FROM query.expression WHERE id = OLD.id;
486
487 -- Create updatable view for cast expressions
488
489 CREATE OR REPLACE VIEW query.expr_xcast AS
490     SELECT
491                 id,
492                 parenthesize,
493                 parent_expr,
494                 seq_no,
495                 left_operand,
496                 cast_type,
497                 negate
498     FROM
499         query.expression
500     WHERE
501         type = 'xcast';
502
503 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
504     ON INSERT TO query.expr_xcast
505     DO INSTEAD
506     INSERT INTO query.expression (
507                 id,
508                 type,
509                 parenthesize,
510                 parent_expr,
511                 seq_no,
512                 left_operand,
513                 cast_type,
514                 negate
515     ) VALUES (
516         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
517         'xcast',
518         COALESCE(NEW.parenthesize, FALSE),
519         NEW.parent_expr,
520         COALESCE(NEW.seq_no, 1),
521                 NEW.left_operand,
522                 NEW.cast_type,
523                 COALESCE(NEW.negate, false)
524     );
525
526 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
527     ON UPDATE TO query.expr_xcast
528     DO INSTEAD
529     UPDATE query.expression SET
530         id = NEW.id,
531         parenthesize = NEW.parenthesize,
532         parent_expr = NEW.parent_expr,
533         seq_no = NEW.seq_no,
534                 left_operand = NEW.left_operand,
535                 cast_type = NEW.cast_type,
536                 negate = NEW.negate
537     WHERE
538         id = OLD.id;
539
540 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
541     ON DELETE TO query.expr_xcast
542     DO INSTEAD
543     DELETE FROM query.expression WHERE id = OLD.id;
544
545 -- Create updatable view for column expressions
546
547 CREATE OR REPLACE VIEW query.expr_xcol AS
548     SELECT
549                 id,
550                 parenthesize,
551                 parent_expr,
552                 seq_no,
553                 table_alias,
554                 column_name,
555                 negate
556     FROM
557         query.expression
558     WHERE
559         type = 'xcol';
560
561 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
562     ON INSERT TO query.expr_xcol
563     DO INSTEAD
564     INSERT INTO query.expression (
565                 id,
566                 type,
567                 parenthesize,
568                 parent_expr,
569                 seq_no,
570                 table_alias,
571                 column_name,
572                 negate
573     ) VALUES (
574         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
575         'xcol',
576         COALESCE(NEW.parenthesize, FALSE),
577         NEW.parent_expr,
578         COALESCE(NEW.seq_no, 1),
579                 NEW.table_alias,
580                 NEW.column_name,
581                 COALESCE(NEW.negate, false)
582     );
583
584 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
585     ON UPDATE TO query.expr_xcol
586     DO INSTEAD
587     UPDATE query.expression SET
588         id = NEW.id,
589         parenthesize = NEW.parenthesize,
590         parent_expr = NEW.parent_expr,
591         seq_no = NEW.seq_no,
592                 table_alias = NEW.table_alias,
593                 column_name = NEW.column_name,
594                 negate = NEW.negate
595     WHERE
596         id = OLD.id;
597
598 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
599     ON DELETE TO query.expr_xcol
600     DO INSTEAD
601     DELETE FROM query.expression WHERE id = OLD.id;
602
603 -- Create updatable view for EXISTS expressions
604
605 CREATE OR REPLACE VIEW query.expr_xex AS
606     SELECT
607                 id,
608                 parenthesize,
609                 parent_expr,
610                 seq_no,
611                 subquery,
612                 negate
613     FROM
614         query.expression
615     WHERE
616         type = 'xex';
617
618 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
619     ON INSERT TO query.expr_xex
620     DO INSTEAD
621     INSERT INTO query.expression (
622                 id,
623                 type,
624                 parenthesize,
625                 parent_expr,
626                 seq_no,
627                 subquery,
628                 negate
629     ) VALUES (
630         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
631         'xex',
632         COALESCE(NEW.parenthesize, FALSE),
633         NEW.parent_expr,
634         COALESCE(NEW.seq_no, 1),
635                 NEW.subquery,
636                 COALESCE(NEW.negate, false)
637     );
638
639 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
640     ON UPDATE TO query.expr_xex
641     DO INSTEAD
642     UPDATE query.expression SET
643         id = NEW.id,
644         parenthesize = NEW.parenthesize,
645         parent_expr = NEW.parent_expr,
646         seq_no = NEW.seq_no,
647                 subquery = NEW.subquery,
648                 negate = NEW.negate
649     WHERE
650         id = OLD.id;
651
652 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
653     ON DELETE TO query.expr_xex
654     DO INSTEAD
655     DELETE FROM query.expression WHERE id = OLD.id;
656
657 -- Create updatable view for field expressions
658
659 CREATE OR REPLACE VIEW query.expr_xfld AS
660     SELECT
661                 id,
662                 parenthesize,
663                 parent_expr,
664                 seq_no,
665                 column_name,
666                 left_operand,
667                 negate
668     FROM
669         query.expression
670     WHERE
671         type = 'xfld';
672
673 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
674     ON INSERT TO query.expr_xfld
675     DO INSTEAD
676     INSERT INTO query.expression (
677                 id,
678                 type,
679                 parenthesize,
680                 parent_expr,
681                 seq_no,
682                 column_name,
683                 left_operand,
684                 negate
685     ) VALUES (
686         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
687         'xfld',
688         COALESCE(NEW.parenthesize, FALSE),
689         NEW.parent_expr,
690         COALESCE(NEW.seq_no, 1),
691                 NEW.column_name,
692                 NEW.left_operand,
693                 COALESCE(NEW.negate, false)
694     );
695
696 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
697     ON UPDATE TO query.expr_xfld
698     DO INSTEAD
699     UPDATE query.expression SET
700         id = NEW.id,
701         parenthesize = NEW.parenthesize,
702         parent_expr = NEW.parent_expr,
703         seq_no = NEW.seq_no,
704                 column_name = NEW.column_name,
705                 left_operand = NEW.left_operand,
706                 negate = NEW.negate
707     WHERE
708         id = OLD.id;
709
710 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
711     ON DELETE TO query.expr_xfld
712     DO INSTEAD
713     DELETE FROM query.expression WHERE id = OLD.id;
714
715 -- Create updatable view for function call expressions
716
717 CREATE OR REPLACE VIEW query.expr_xfunc AS
718     SELECT
719                 id,
720                 parenthesize,
721                 parent_expr,
722                 seq_no,
723                 function_id,
724                 negate
725     FROM
726         query.expression
727     WHERE
728         type = 'xfunc';
729
730 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
731     ON INSERT TO query.expr_xfunc
732     DO INSTEAD
733     INSERT INTO query.expression (
734                 id,
735                 type,
736                 parenthesize,
737                 parent_expr,
738                 seq_no,
739                 function_id,
740                 negate
741     ) VALUES (
742         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
743         'xfunc',
744         COALESCE(NEW.parenthesize, FALSE),
745         NEW.parent_expr,
746         COALESCE(NEW.seq_no, 1),
747                 NEW.function_id,
748                 COALESCE(NEW.negate, false)
749     );
750
751 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
752     ON UPDATE TO query.expr_xfunc
753     DO INSTEAD
754     UPDATE query.expression SET
755         id = NEW.id,
756         parenthesize = NEW.parenthesize,
757         parent_expr = NEW.parent_expr,
758         seq_no = NEW.seq_no,
759                 function_id = NEW.function_id,
760                 negate = NEW.negate
761     WHERE
762         id = OLD.id;
763
764 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
765     ON DELETE TO query.expr_xfunc
766     DO INSTEAD
767     DELETE FROM query.expression WHERE id = OLD.id;
768
769 -- Create updatable view for IN expressions
770
771 CREATE OR REPLACE VIEW query.expr_xin AS
772     SELECT
773                 id,
774                 parenthesize,
775                 parent_expr,
776                 seq_no,
777                 left_operand,
778                 subquery,
779                 negate
780     FROM
781         query.expression
782     WHERE
783         type = 'xin';
784
785 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
786     ON INSERT TO query.expr_xin
787     DO INSTEAD
788     INSERT INTO query.expression (
789                 id,
790                 type,
791                 parenthesize,
792                 parent_expr,
793                 seq_no,
794                 left_operand,
795                 subquery,
796                 negate
797     ) VALUES (
798         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
799         'xin',
800         COALESCE(NEW.parenthesize, FALSE),
801         NEW.parent_expr,
802         COALESCE(NEW.seq_no, 1),
803                 NEW.left_operand,
804                 NEW.subquery,
805                 COALESCE(NEW.negate, false)
806     );
807
808 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
809     ON UPDATE TO query.expr_xin
810     DO INSTEAD
811     UPDATE query.expression SET
812         id = NEW.id,
813         parenthesize = NEW.parenthesize,
814         parent_expr = NEW.parent_expr,
815         seq_no = NEW.seq_no,
816                 left_operand = NEW.left_operand,
817                 subquery = NEW.subquery,
818                 negate = NEW.negate
819     WHERE
820         id = OLD.id;
821
822 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
823     ON DELETE TO query.expr_xin
824     DO INSTEAD
825     DELETE FROM query.expression WHERE id = OLD.id;
826
827 -- Create updatable view for IS NULL expressions
828
829 CREATE OR REPLACE VIEW query.expr_xisnull AS
830     SELECT
831                 id,
832                 parenthesize,
833                 parent_expr,
834                 seq_no,
835                 left_operand,
836                 negate
837     FROM
838         query.expression
839     WHERE
840         type = 'xisnull';
841
842 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
843     ON INSERT TO query.expr_xisnull
844     DO INSTEAD
845     INSERT INTO query.expression (
846                 id,
847                 type,
848                 parenthesize,
849                 parent_expr,
850                 seq_no,
851                 left_operand,
852                 negate
853     ) VALUES (
854         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
855         'xisnull',
856         COALESCE(NEW.parenthesize, FALSE),
857         NEW.parent_expr,
858         COALESCE(NEW.seq_no, 1),
859                 NEW.left_operand,
860                 COALESCE(NEW.negate, false)
861     );
862
863 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
864     ON UPDATE TO query.expr_xisnull
865     DO INSTEAD
866     UPDATE query.expression SET
867         id = NEW.id,
868         parenthesize = NEW.parenthesize,
869         parent_expr = NEW.parent_expr,
870         seq_no = NEW.seq_no,
871                 left_operand = NEW.left_operand,
872                 negate = NEW.negate
873     WHERE
874         id = OLD.id;
875
876 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
877     ON DELETE TO query.expr_xisnull
878     DO INSTEAD
879     DELETE FROM query.expression WHERE id = OLD.id;
880
881 -- Create updatable view for NULL expressions
882
883 CREATE OR REPLACE VIEW query.expr_xnull AS
884     SELECT
885                 id,
886                 parenthesize,
887                 parent_expr,
888                 seq_no,
889                 negate
890     FROM
891         query.expression
892     WHERE
893         type = 'xnull';
894
895 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
896     ON INSERT TO query.expr_xnull
897     DO INSTEAD
898     INSERT INTO query.expression (
899                 id,
900                 type,
901                 parenthesize,
902                 parent_expr,
903                 seq_no,
904                 negate
905     ) VALUES (
906         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
907         'xnull',
908         COALESCE(NEW.parenthesize, FALSE),
909         NEW.parent_expr,
910         COALESCE(NEW.seq_no, 1),
911                 COALESCE(NEW.negate, false)
912     );
913
914 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
915     ON UPDATE TO query.expr_xnull
916     DO INSTEAD
917     UPDATE query.expression SET
918         id = NEW.id,
919         parenthesize = NEW.parenthesize,
920         parent_expr = NEW.parent_expr,
921         seq_no = NEW.seq_no,
922                 negate = NEW.negate
923     WHERE
924         id = OLD.id;
925
926 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
927     ON DELETE TO query.expr_xnull
928     DO INSTEAD
929     DELETE FROM query.expression WHERE id = OLD.id;
930
931 -- Create updatable view for numeric literal expressions
932
933 CREATE OR REPLACE VIEW query.expr_xnum AS
934     SELECT
935                 id,
936                 parenthesize,
937                 parent_expr,
938                 seq_no,
939                 literal
940     FROM
941         query.expression
942     WHERE
943         type = 'xnum';
944
945 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
946     ON INSERT TO query.expr_xnum
947     DO INSTEAD
948     INSERT INTO query.expression (
949                 id,
950                 type,
951                 parenthesize,
952                 parent_expr,
953                 seq_no,
954                 literal
955     ) VALUES (
956         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
957         'xnum',
958         COALESCE(NEW.parenthesize, FALSE),
959         NEW.parent_expr,
960         COALESCE(NEW.seq_no, 1),
961         NEW.literal
962     );
963
964 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
965     ON UPDATE TO query.expr_xnum
966     DO INSTEAD
967     UPDATE query.expression SET
968         id = NEW.id,
969         parenthesize = NEW.parenthesize,
970         parent_expr = NEW.parent_expr,
971         seq_no = NEW.seq_no,
972         literal = NEW.literal
973     WHERE
974         id = OLD.id;
975
976 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
977     ON DELETE TO query.expr_xnum
978     DO INSTEAD
979     DELETE FROM query.expression WHERE id = OLD.id;
980
981 -- Create updatable view for operator expressions
982
983 CREATE OR REPLACE VIEW query.expr_xop AS
984     SELECT
985                 id,
986                 parenthesize,
987                 parent_expr,
988                 seq_no,
989                 left_operand,
990                 operator,
991                 right_operand,
992                 negate
993     FROM
994         query.expression
995     WHERE
996         type = 'xop';
997
998 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
999     ON INSERT TO query.expr_xop
1000     DO INSTEAD
1001     INSERT INTO query.expression (
1002                 id,
1003                 type,
1004                 parenthesize,
1005                 parent_expr,
1006                 seq_no,
1007                 left_operand,
1008                 operator,
1009                 right_operand,
1010                 negate
1011     ) VALUES (
1012         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1013         'xop',
1014         COALESCE(NEW.parenthesize, FALSE),
1015         NEW.parent_expr,
1016         COALESCE(NEW.seq_no, 1),
1017                 NEW.left_operand,
1018                 NEW.operator,
1019                 NEW.right_operand,
1020                 COALESCE(NEW.negate, false)
1021     );
1022
1023 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
1024     ON UPDATE TO query.expr_xop
1025     DO INSTEAD
1026     UPDATE query.expression SET
1027         id = NEW.id,
1028         parenthesize = NEW.parenthesize,
1029         parent_expr = NEW.parent_expr,
1030         seq_no = NEW.seq_no,
1031                 left_operand = NEW.left_operand,
1032                 operator = NEW.operator,
1033                 right_operand = NEW.right_operand,
1034                 negate = NEW.negate
1035     WHERE
1036         id = OLD.id;
1037
1038 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1039     ON DELETE TO query.expr_xop
1040     DO INSTEAD
1041     DELETE FROM query.expression WHERE id = OLD.id;
1042
1043 -- Create updatable view for series expressions,
1044 -- i.e. series of expressions separated by operators
1045
1046 CREATE OR REPLACE VIEW query.expr_xser AS
1047     SELECT
1048                 id,
1049                 parenthesize,
1050                 parent_expr,
1051                 seq_no,
1052                 operator,
1053                 negate
1054     FROM
1055         query.expression
1056     WHERE
1057         type = 'xser';
1058
1059 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1060     ON INSERT TO query.expr_xser
1061     DO INSTEAD
1062     INSERT INTO query.expression (
1063                 id,
1064                 type,
1065                 parenthesize,
1066                 parent_expr,
1067                 seq_no,
1068                 operator,
1069                 negate
1070     ) VALUES (
1071         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1072         'xser',
1073         COALESCE(NEW.parenthesize, FALSE),
1074         NEW.parent_expr,
1075         COALESCE(NEW.seq_no, 1),
1076                 NEW.operator,
1077                 COALESCE(NEW.negate, false)
1078     );
1079
1080 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1081     ON UPDATE TO query.expr_xser
1082     DO INSTEAD
1083     UPDATE query.expression SET
1084         id = NEW.id,
1085         parenthesize = NEW.parenthesize,
1086         parent_expr = NEW.parent_expr,
1087         seq_no = NEW.seq_no,
1088                 operator = NEW.operator,
1089                 negate = NEW.negate
1090     WHERE
1091         id = OLD.id;
1092
1093 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1094     ON DELETE TO query.expr_xser
1095     DO INSTEAD
1096     DELETE FROM query.expression WHERE id = OLD.id;
1097
1098 -- Create updatable view for string literal expressions
1099
1100 CREATE OR REPLACE VIEW query.expr_xstr AS
1101     SELECT
1102         id,
1103         parenthesize,
1104         parent_expr,
1105         seq_no,
1106         literal
1107     FROM
1108         query.expression
1109     WHERE
1110         type = 'xstr';
1111
1112 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1113     ON INSERT TO query.expr_xstr
1114     DO INSTEAD
1115     INSERT INTO query.expression (
1116         id,
1117         type,
1118         parenthesize,
1119         parent_expr,
1120         seq_no,
1121         literal
1122     ) VALUES (
1123         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1124         'xstr',
1125         COALESCE(NEW.parenthesize, FALSE),
1126         NEW.parent_expr,
1127         COALESCE(NEW.seq_no, 1),
1128         NEW.literal
1129     );
1130
1131 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1132     ON UPDATE TO query.expr_xstr
1133     DO INSTEAD
1134     UPDATE query.expression SET
1135         id = NEW.id,
1136         parenthesize = NEW.parenthesize,
1137         parent_expr = NEW.parent_expr,
1138         seq_no = NEW.seq_no,
1139         literal = NEW.literal
1140     WHERE
1141         id = OLD.id;
1142
1143 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1144     ON DELETE TO query.expr_xstr
1145     DO INSTEAD
1146     DELETE FROM query.expression WHERE id = OLD.id;
1147
1148 -- Create updatable view for subquery expressions
1149
1150 CREATE OR REPLACE VIEW query.expr_xsubq AS
1151     SELECT
1152                 id,
1153                 parenthesize,
1154                 parent_expr,
1155                 seq_no,
1156                 subquery,
1157                 negate
1158     FROM
1159         query.expression
1160     WHERE
1161         type = 'xsubq';
1162
1163 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1164     ON INSERT TO query.expr_xsubq
1165     DO INSTEAD
1166     INSERT INTO query.expression (
1167                 id,
1168                 type,
1169                 parenthesize,
1170                 parent_expr,
1171                 seq_no,
1172                 subquery,
1173                 negate
1174     ) VALUES (
1175         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1176         'xsubq',
1177         COALESCE(NEW.parenthesize, FALSE),
1178         NEW.parent_expr,
1179         COALESCE(NEW.seq_no, 1),
1180                 NEW.subquery,
1181                 COALESCE(NEW.negate, false)
1182     );
1183
1184 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1185     ON UPDATE TO query.expr_xsubq
1186     DO INSTEAD
1187     UPDATE query.expression SET
1188         id = NEW.id,
1189         parenthesize = NEW.parenthesize,
1190         parent_expr = NEW.parent_expr,
1191         seq_no = NEW.seq_no,
1192                 subquery = NEW.subquery,
1193                 negate = NEW.negate
1194     WHERE
1195         id = OLD.id;
1196
1197 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1198     ON DELETE TO query.expr_xsubq
1199     DO INSTEAD
1200     DELETE FROM query.expression WHERE id = OLD.id;
1201
1202 COMMIT;