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