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