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