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