Acq: correct a mistake in r16464
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 008.schema.query.sql
1 -- Script to create the query schema and the tables therein
2
3 BEGIN;
4
5 DROP SCHEMA IF EXISTS query CASCADE;
6 CREATE SCHEMA query;
7 COMMENT ON SCHEMA actor IS $$
8 /*
9  * Copyright (C) 2009  Equinox Software, Inc. / Georgia Public Library Service
10  * Scott McKellar <scott@esilibrary.com>
11  *
12  * Schema: query
13  *
14  * Contains tables designed to represent user-defined queries for
15  * reports and the like.
16  *
17  * ****
18  *
19  * This program is free software; you can redistribute it and/or
20  * modify it under the terms of the GNU General Public License
21  * as published by the Free Software Foundation; either version 2
22  * of the License, or (at your option) any later version.
23  *
24  * This program is distributed in the hope that it will be useful,
25  * but WITHOUT ANY WARRANTY; without even the implied warranty of
26  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
27  * GNU General Public License for more details.
28  */
29 $$;
30
31 CREATE TABLE  query.stored_query (
32         id            SERIAL         PRIMARY KEY,
33         type          TEXT           NOT NULL CONSTRAINT query_type CHECK
34                                      ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ),
35         use_all       BOOLEAN        NOT NULL DEFAULT FALSE,
36         use_distinct  BOOLEAN        NOT NULL DEFAULT FALSE,
37         from_clause   INT,           --REFERENCES query.from_clause
38                                      --DEFERRABLE INITIALLY DEFERRED,
39         where_clause  INT,           --REFERENCES query.expression
40                                      --DEFERRABLE INITIALLY DEFERRED,
41         having_clause INT            --REFERENCES query.expression
42                                      --DEFERRABLE INITIALLY DEFERRED
43 );
44
45 -- (Foreign keys to be defined later after other tables are created)
46
47 CREATE TABLE query.query_sequence (
48         id              SERIAL            PRIMARY KEY,
49         parent_query    INT               NOT NULL
50                                           REFERENCES query.stored_query
51                                                                           ON DELETE CASCADE
52                                                                           DEFERRABLE INITIALLY DEFERRED,
53         seq_no          INT               NOT NULL,
54         child_query     INT               NOT NULL
55                                           REFERENCES query.stored_query
56                                                                           ON DELETE CASCADE
57                                                                           DEFERRABLE INITIALLY DEFERRED,
58         CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
59 );
60
61 CREATE TABLE query.datatype (
62         id              SERIAL            PRIMARY KEY,
63         datatype_name   TEXT              NOT NULL UNIQUE,
64         is_numeric      BOOL              NOT NULL DEFAULT FALSE,
65         is_composite    BOOL              NOT NULL DEFAULT FALSE,
66         CONSTRAINT qdt_comp_not_num CHECK
67         ( is_numeric IS FALSE OR is_composite IS FALSE )
68 );
69
70 CREATE TABLE query.subfield (
71         id              SERIAL            PRIMARY KEY,
72         composite_type  INT               NOT NULL
73                                           REFERENCES query.datatype(id)
74                                           ON DELETE CASCADE
75                                           DEFERRABLE INITIALLY DEFERRED,
76         seq_no          INT               NOT NULL
77                                           CONSTRAINT qsf_pos_seq_no
78                                           CHECK( seq_no > 0 ),
79         subfield_type   INT               NOT NULL
80                                           REFERENCES query.datatype(id)
81                                           DEFERRABLE INITIALLY DEFERRED,
82         CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
83 );
84
85 CREATE TABLE query.function_sig (
86         id              SERIAL            PRIMARY KEY,
87         function_name   TEXT              NOT NULL,
88         return_type     INT               REFERENCES query.datatype(id)
89                                           DEFERRABLE INITIALLY DEFERRED,
90         is_aggregate    BOOL              NOT NULL DEFAULT FALSE,
91         CONSTRAINT qfd_rtn_or_aggr CHECK
92         ( return_type IS NULL OR is_aggregate = FALSE )
93 );
94
95 CREATE INDEX query_function_sig_name_idx 
96         ON query.function_sig (function_name);
97
98 CREATE TABLE query.function_param_def (
99         id              SERIAL            PRIMARY KEY,
100         function_id     INT               NOT NULL
101                                           REFERENCES query.function_sig( id )
102                                           ON DELETE CASCADE
103                                           DEFERRABLE INITIALLY DEFERRED,
104         seq_no          INT               NOT NULL
105                                           CONSTRAINT qfpd_pos_seq_no CHECK
106                                           ( seq_no > 0 ),
107         datatype        INT               NOT NULL
108                                           REFERENCES query.datatype( id )
109                                           DEFERRABLE INITIALLY DEFERRED,
110         CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
111 );
112
113 CREATE TABLE query.bind_variable (
114         name          TEXT             PRIMARY KEY,
115         type          TEXT             NOT NULL
116                                            CONSTRAINT bind_variable_type CHECK
117                                            ( type in ( 'string', 'number', 'string_list', 'number_list' )),
118         description   TEXT             NOT NULL,
119         default_value TEXT,            -- to be encoded in JSON
120         label         TEXT             NOT NULL
121 );
122
123 CREATE TABLE query.expression (
124         id            SERIAL        PRIMARY KEY,
125         type          TEXT          NOT NULL CONSTRAINT expression_type CHECK
126                                     ( type IN (
127                                         'xbet',    -- between
128                                                                         'xbind',   -- bind variable
129                                                                         'xbool',   -- boolean
130                                         'xcase',   -- case
131                                                                         'xcast',   -- cast
132                                                                         'xcol',    -- column
133                                                                         'xex',     -- exists
134                                                                         'xfld',    -- field
135                                                                         'xfunc',   -- function
136                                                                         'xin',     -- in
137                                         'xnull',   -- null
138                                                                         'xnum',    -- number
139                                                                         'xop',     -- operator
140                                                                         'xstr',    -- string
141                                                 'xsubq'    -- subquery
142                                                                 ) ),
143         parenthesize  BOOL          NOT NULL DEFAULT FALSE,
144         parent_expr   INT           REFERENCES query.expression
145                                     ON DELETE CASCADE
146                                     DEFERRABLE INITIALLY DEFERRED,
147         seq_no        INT           NOT NULL DEFAULT 1,
148         literal       TEXT,
149         table_alias   TEXT,
150         column_name   TEXT,
151         left_operand  INT           REFERENCES query.expression
152                                     DEFERRABLE INITIALLY DEFERRED,
153         operator      TEXT,
154         right_operand INT           REFERENCES query.expression
155                                     DEFERRABLE INITIALLY DEFERRED,
156         function_id   INT           REFERENCES query.function_sig
157                                     DEFERRABLE INITIALLY DEFERRED,
158         subquery      INT           REFERENCES query.stored_query
159                                     DEFERRABLE INITIALLY DEFERRED,
160         cast_type     INT           REFERENCES query.datatype
161                                     DEFERRABLE INITIALLY DEFERRED,
162         negate        BOOL          NOT NULL DEFAULT FALSE,
163         bind_variable TEXT          REFERENCES query.bind_variable
164                                     DEFERRABLE INITIALLY DEFERRED
165 );
166
167 CREATE UNIQUE INDEX query_expr_parent_seq
168         ON query.expression( parent_expr, seq_no )
169         WHERE parent_expr IS NOT NULL;
170
171 -- Due to some circular references, the following foreign key definitions
172 -- had to be deferred until query.expression existed:
173
174 ALTER TABLE query.stored_query
175         ADD FOREIGN KEY ( where_clause )
176         REFERENCES query.expression( id )
177         DEFERRABLE INITIALLY DEFERRED;
178
179 ALTER TABLE query.stored_query
180         ADD FOREIGN KEY ( having_clause )
181         REFERENCES query.expression( id )
182         DEFERRABLE INITIALLY DEFERRED;
183
184 CREATE TABLE query.case_branch (
185         id            SERIAL        PRIMARY KEY,
186         parent_expr   INT           NOT NULL REFERENCES query.expression
187                                     ON DELETE CASCADE
188                                     DEFERRABLE INITIALLY DEFERRED,
189         seq_no        INT           NOT NULL,
190         condition     INT           REFERENCES query.expression
191                                     DEFERRABLE INITIALLY DEFERRED,
192         result        INT           NOT NULL REFERENCES query.expression
193                                     DEFERRABLE INITIALLY DEFERRED,
194         CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
195 );
196
197 CREATE TABLE query.from_relation (
198         id               SERIAL        PRIMARY KEY,
199         type             TEXT          NOT NULL CONSTRAINT relation_type CHECK (
200                                            type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
201         table_name       TEXT,
202         class_name       TEXT,
203         subquery         INT           REFERENCES query.stored_query,
204         function_call    INT           REFERENCES query.expression,
205         table_alias      TEXT,
206         parent_relation  INT           REFERENCES query.from_relation
207                                        ON DELETE CASCADE
208                                        DEFERRABLE INITIALLY DEFERRED,
209         seq_no           INT           NOT NULL DEFAULT 1,
210         join_type        TEXT          CONSTRAINT good_join_type CHECK (
211                                            join_type IS NULL OR join_type IN
212                                            ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
213                                        ),
214         on_clause        INT           REFERENCES query.expression
215                                        DEFERRABLE INITIALLY DEFERRED,
216         CONSTRAINT join_or_core CHECK (
217             ( parent_relation IS NULL AND join_type IS NULL 
218               AND on_clause IS NULL )
219             OR
220             ( parent_relation IS NOT NULL AND join_type IS NOT NULL
221               AND on_clause IS NOT NULL )
222         )
223 );
224
225 CREATE UNIQUE INDEX from_parent_seq
226         ON query.from_relation( parent_relation, seq_no )
227         WHERE parent_relation IS NOT NULL;
228
229 -- The following foreign key had to be deferred until
230 -- query.from_relation existed
231
232 ALTER TABLE query.stored_query
233         ADD FOREIGN KEY (from_clause)
234         REFERENCES query.from_relation
235         DEFERRABLE INITIALLY DEFERRED;
236
237 CREATE TABLE query.record_column (
238         id            SERIAL            PRIMARY KEY,
239         from_relation INT               NOT NULL REFERENCES query.from_relation
240                                         ON DELETE CASCADE
241                                         DEFERRABLE INITIALLY DEFERRED,
242         seq_no        INT               NOT NULL,
243         column_name   TEXT              NOT NULL,
244     column_type   INT               NOT NULL REFERENCES query.datatype
245                                     ON DELETE CASCADE
246                                     DEFERRABLE INITIALLY DEFERRED,
247         CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
248 );
249
250 CREATE TABLE query.select_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                                         DEFERRABLE INITIALLY DEFERRED,
258         column_alias     TEXT,
259         grouped_by       BOOL           NOT NULL DEFAULT FALSE,
260         CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
261 );
262
263 CREATE TABLE query.order_by_item (
264         id               SERIAL         PRIMARY KEY,
265         stored_query     INT            NOT NULL REFERENCES query.stored_query
266                                         ON DELETE CASCADE
267                                         DEFERRABLE INITIALLY DEFERRED,
268         seq_no           INT            NOT NULL,
269         expression       INT            NOT NULL REFERENCES query.expression
270                                         ON DELETE CASCADE
271                                         DEFERRABLE INITIALLY DEFERRED,
272         CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
273 );
274
275 -- Create updatable views -------------------------------------------
276
277 -- Create updatable view for BETWEEN expressions
278
279 CREATE OR REPLACE VIEW query.expr_xbet AS
280     SELECT
281                 id,
282                 parenthesize,
283                 parent_expr,
284                 seq_no,
285                 negate
286     FROM
287         query.expression
288     WHERE
289         type = 'xbet';
290
291 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
292     ON INSERT TO query.expr_xbet
293     DO INSTEAD
294     INSERT INTO query.expression (
295                 id,
296                 type,
297                 parenthesize,
298                 parent_expr,
299                 seq_no,
300                 negate
301     ) VALUES (
302         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
303         'xbet',
304         COALESCE(NEW.parenthesize, FALSE),
305         NEW.parent_expr,
306         COALESCE(NEW.seq_no, 1),
307                 NEW.negate
308     );
309
310 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
311     ON UPDATE TO query.expr_xbet
312     DO INSTEAD
313     UPDATE query.expression SET
314         id = NEW.id,
315         parenthesize = NEW.parenthesize,
316         parent_expr = NEW.parent_expr,
317         seq_no = NEW.seq_no,
318                 negate = NEW.negate
319     WHERE
320         id = OLD.id;
321
322 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
323     ON DELETE TO query.expr_xbet
324     DO INSTEAD
325     DELETE FROM query.expression WHERE id = OLD.id;
326
327 -- Create updatable view for bind variable expressions
328
329 CREATE OR REPLACE VIEW query.expr_xbind AS
330     SELECT
331                 id,
332                 parenthesize,
333                 parent_expr,
334                 seq_no,
335                 bind_variable
336     FROM
337         query.expression
338     WHERE
339         type = 'xbind';
340
341 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
342     ON INSERT TO query.expr_xbind
343     DO INSTEAD
344     INSERT INTO query.expression (
345                 id,
346                 type,
347                 parenthesize,
348                 parent_expr,
349                 seq_no,
350                 bind_variable
351     ) VALUES (
352         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
353         'xbind',
354         COALESCE(NEW.parenthesize, FALSE),
355         NEW.parent_expr,
356         COALESCE(NEW.seq_no, 1),
357                 NEW.bind_variable
358     );
359
360 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
361     ON UPDATE TO query.expr_xbind
362     DO INSTEAD
363     UPDATE query.expression SET
364         id = NEW.id,
365         parenthesize = NEW.parenthesize,
366         parent_expr = NEW.parent_expr,
367         seq_no = NEW.seq_no,
368                 bind_variable = NEW.bind_variable
369     WHERE
370         id = OLD.id;
371
372 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
373     ON DELETE TO query.expr_xbind
374     DO INSTEAD
375     DELETE FROM query.expression WHERE id = OLD.id;
376
377 -- Create updatable view for bind variable expressions
378
379 CREATE OR REPLACE VIEW query.expr_xbind AS
380     SELECT
381                 id,
382                 parenthesize,
383                 parent_expr,
384                 seq_no,
385                 bind_variable
386     FROM
387         query.expression
388     WHERE
389         type = 'xbind';
390
391 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
392     ON INSERT TO query.expr_xbind
393     DO INSTEAD
394     INSERT INTO query.expression (
395                 id,
396                 type,
397                 parenthesize,
398                 parent_expr,
399                 seq_no,
400                 bind_variable
401     ) VALUES (
402         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
403         'xbind',
404         COALESCE(NEW.parenthesize, FALSE),
405         NEW.parent_expr,
406         COALESCE(NEW.seq_no, 1),
407                 NEW.bind_variable
408     );
409
410 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
411     ON UPDATE TO query.expr_xbind
412     DO INSTEAD
413     UPDATE query.expression SET
414         id = NEW.id,
415         parenthesize = NEW.parenthesize,
416         parent_expr = NEW.parent_expr,
417         seq_no = NEW.seq_no,
418                 bind_variable = NEW.bind_variable
419     WHERE
420         id = OLD.id;
421
422 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
423     ON DELETE TO query.expr_xbind
424     DO INSTEAD
425     DELETE FROM query.expression WHERE id = OLD.id;
426
427 -- Create updatable view for boolean expressions
428
429 CREATE OR REPLACE VIEW query.expr_xbool AS
430     SELECT
431                 id,
432                 parenthesize,
433                 parent_expr,
434                 seq_no,
435                 literal,
436                 negate
437     FROM
438         query.expression
439     WHERE
440         type = 'xbool';
441
442 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
443     ON INSERT TO query.expr_xbool
444     DO INSTEAD
445     INSERT INTO query.expression (
446                 id,
447                 type,
448                 parenthesize,
449                 parent_expr,
450                 seq_no,
451                 literal,
452                 negate
453     ) VALUES (
454         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
455         'xbool',
456         COALESCE(NEW.parenthesize, FALSE),
457         NEW.parent_expr,
458         COALESCE(NEW.seq_no, 1),
459         NEW.literal,
460                 NEW.negate
461     );
462
463 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
464     ON UPDATE TO query.expr_xbool
465     DO INSTEAD
466     UPDATE query.expression SET
467         id = NEW.id,
468         parenthesize = NEW.parenthesize,
469         parent_expr = NEW.parent_expr,
470         seq_no = NEW.seq_no,
471         literal = NEW.literal,
472                 negate = NEW.negate
473     WHERE
474         id = OLD.id;
475
476 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
477     ON DELETE TO query.expr_xbool
478     DO INSTEAD
479     DELETE FROM query.expression WHERE id = OLD.id;
480
481 -- Create updatable view for CASE expressions
482
483 CREATE OR REPLACE VIEW query.expr_xcase AS
484     SELECT
485                 id,
486                 parenthesize,
487                 parent_expr,
488                 seq_no,
489                 negate
490     FROM
491         query.expression
492     WHERE
493         type = 'xcase';
494
495 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
496     ON INSERT TO query.expr_xcase
497     DO INSTEAD
498     INSERT INTO query.expression (
499                 id,
500                 type,
501                 parenthesize,
502                 parent_expr,
503                 seq_no,
504                 negate
505     ) VALUES (
506         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
507         'xcase',
508         COALESCE(NEW.parenthesize, FALSE),
509         NEW.parent_expr,
510         COALESCE(NEW.seq_no, 1),
511                 NEW.negate
512     );
513
514 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
515     ON UPDATE TO query.expr_xcase
516     DO INSTEAD
517     UPDATE query.expression SET
518         id = NEW.id,
519         parenthesize = NEW.parenthesize,
520         parent_expr = NEW.parent_expr,
521         seq_no = NEW.seq_no,
522                 negate = NEW.negate
523     WHERE
524         id = OLD.id;
525
526 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
527     ON DELETE TO query.expr_xcase
528     DO INSTEAD
529     DELETE FROM query.expression WHERE id = OLD.id;
530
531 -- Create updatable view for cast expressions
532
533 CREATE OR REPLACE VIEW query.expr_xcast AS
534     SELECT
535                 id,
536                 parenthesize,
537                 parent_expr,
538                 seq_no,
539                 left_operand,
540                 cast_type,
541                 negate
542     FROM
543         query.expression
544     WHERE
545         type = 'xcast';
546
547 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
548     ON INSERT TO query.expr_xcast
549     DO INSTEAD
550     INSERT INTO query.expression (
551                 id,
552                 type,
553                 parenthesize,
554                 parent_expr,
555                 seq_no,
556                 left_operand,
557                 cast_type,
558                 negate
559     ) VALUES (
560         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
561         'xcast',
562         COALESCE(NEW.parenthesize, FALSE),
563         NEW.parent_expr,
564         COALESCE(NEW.seq_no, 1),
565                 NEW.left_operand,
566                 NEW.cast_type,
567                 NEW.negate
568     );
569
570 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
571     ON UPDATE TO query.expr_xcast
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                 left_operand = NEW.left_operand,
579                 cast_type = NEW.cast_type,
580                 negate = NEW.negate
581     WHERE
582         id = OLD.id;
583
584 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
585     ON DELETE TO query.expr_xcast
586     DO INSTEAD
587     DELETE FROM query.expression WHERE id = OLD.id;
588
589 -- Create updatable view for column expressions
590
591 CREATE OR REPLACE VIEW query.expr_xcol AS
592     SELECT
593                 id,
594                 parenthesize,
595                 parent_expr,
596                 seq_no,
597                 table_alias,
598                 column_name,
599                 negate
600     FROM
601         query.expression
602     WHERE
603         type = 'xcol';
604
605 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
606     ON INSERT TO query.expr_xcol
607     DO INSTEAD
608     INSERT INTO query.expression (
609                 id,
610                 type,
611                 parenthesize,
612                 parent_expr,
613                 seq_no,
614                 table_alias,
615                 column_name,
616                 negate
617     ) VALUES (
618         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
619         'xcol',
620         COALESCE(NEW.parenthesize, FALSE),
621         NEW.parent_expr,
622         COALESCE(NEW.seq_no, 1),
623                 NEW.table_alias,
624                 NEW.column_name,
625                 NEW.negate
626     );
627
628 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
629     ON UPDATE TO query.expr_xcol
630     DO INSTEAD
631     UPDATE query.expression SET
632         id = NEW.id,
633         parenthesize = NEW.parenthesize,
634         parent_expr = NEW.parent_expr,
635         seq_no = NEW.seq_no,
636                 table_alias = NEW.table_alias,
637                 column_name = NEW.column_name,
638                 negate = NEW.negate
639     WHERE
640         id = OLD.id;
641
642 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
643     ON DELETE TO query.expr_xcol
644     DO INSTEAD
645     DELETE FROM query.expression WHERE id = OLD.id;
646
647 -- Create updatable view for EXISTS expressions
648
649 CREATE OR REPLACE VIEW query.expr_xex AS
650     SELECT
651                 id,
652                 parenthesize,
653                 parent_expr,
654                 seq_no,
655                 subquery,
656                 negate
657     FROM
658         query.expression
659     WHERE
660         type = 'xex';
661
662 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
663     ON INSERT TO query.expr_xex
664     DO INSTEAD
665     INSERT INTO query.expression (
666                 id,
667                 type,
668                 parenthesize,
669                 parent_expr,
670                 seq_no,
671                 subquery,
672                 negate
673     ) VALUES (
674         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
675         'xex',
676         COALESCE(NEW.parenthesize, FALSE),
677         NEW.parent_expr,
678         COALESCE(NEW.seq_no, 1),
679                 NEW.subquery,
680                 NEW.negate
681     );
682
683 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
684     ON UPDATE TO query.expr_xex
685     DO INSTEAD
686     UPDATE query.expression SET
687         id = NEW.id,
688         parenthesize = NEW.parenthesize,
689         parent_expr = NEW.parent_expr,
690         seq_no = NEW.seq_no,
691                 subquery = NEW.subquery,
692                 negate = NEW.negate
693     WHERE
694         id = OLD.id;
695
696 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
697     ON DELETE TO query.expr_xex
698     DO INSTEAD
699     DELETE FROM query.expression WHERE id = OLD.id;
700
701 -- Create updatable view for field expressions
702
703 CREATE OR REPLACE VIEW query.expr_xfld AS
704     SELECT
705                 id,
706                 parenthesize,
707                 parent_expr,
708                 seq_no,
709                 column_name,
710                 left_operand,
711                 negate
712     FROM
713         query.expression
714     WHERE
715         type = 'xfld';
716
717 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
718     ON INSERT TO query.expr_xfld
719     DO INSTEAD
720     INSERT INTO query.expression (
721                 id,
722                 type,
723                 parenthesize,
724                 parent_expr,
725                 seq_no,
726                 column_name,
727                 left_operand,
728                 negate
729     ) VALUES (
730         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
731         'xfld',
732         COALESCE(NEW.parenthesize, FALSE),
733         NEW.parent_expr,
734         COALESCE(NEW.seq_no, 1),
735                 NEW.column_name,
736                 NEW.left_operand,
737                 NEW.negate
738     );
739
740 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
741     ON UPDATE TO query.expr_xfld
742     DO INSTEAD
743     UPDATE query.expression SET
744         id = NEW.id,
745         parenthesize = NEW.parenthesize,
746         parent_expr = NEW.parent_expr,
747         seq_no = NEW.seq_no,
748                 column_name = NEW.column_name,
749                 left_operand = NEW.left_operand,
750                 negate = NEW.negate
751     WHERE
752         id = OLD.id;
753
754 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
755     ON DELETE TO query.expr_xfld
756     DO INSTEAD
757     DELETE FROM query.expression WHERE id = OLD.id;
758
759 -- Create updatable view for function call expressions
760
761 CREATE OR REPLACE VIEW query.expr_xfunc AS
762     SELECT
763                 id,
764                 parenthesize,
765                 parent_expr,
766                 seq_no,
767                 function_id,
768                 negate
769     FROM
770         query.expression
771     WHERE
772         type = 'xfunc';
773
774 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
775     ON INSERT TO query.expr_xfunc
776     DO INSTEAD
777     INSERT INTO query.expression (
778                 id,
779                 type,
780                 parenthesize,
781                 parent_expr,
782                 seq_no,
783                 function_id,
784                 negate
785     ) VALUES (
786         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
787         'xfunc',
788         COALESCE(NEW.parenthesize, FALSE),
789         NEW.parent_expr,
790         COALESCE(NEW.seq_no, 1),
791                 NEW.function_id,
792                 NEW.negate
793     );
794
795 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
796     ON UPDATE TO query.expr_xfunc
797     DO INSTEAD
798     UPDATE query.expression SET
799         id = NEW.id,
800         parenthesize = NEW.parenthesize,
801         parent_expr = NEW.parent_expr,
802         seq_no = NEW.seq_no,
803                 function_id = NEW.function_id,
804                 negate = NEW.negate
805     WHERE
806         id = OLD.id;
807
808 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
809     ON DELETE TO query.expr_xfunc
810     DO INSTEAD
811     DELETE FROM query.expression WHERE id = OLD.id;
812
813 -- Create updatable view for IN expressions
814
815 CREATE OR REPLACE VIEW query.expr_xin AS
816     SELECT
817                 id,
818                 parenthesize,
819                 parent_expr,
820                 seq_no,
821                 left_operand,
822                 subquery,
823                 negate
824     FROM
825         query.expression
826     WHERE
827         type = 'xin';
828
829 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
830     ON INSERT TO query.expr_xin
831     DO INSTEAD
832     INSERT INTO query.expression (
833                 id,
834                 type,
835                 parenthesize,
836                 parent_expr,
837                 seq_no,
838                 left_operand,
839                 subquery,
840                 negate
841     ) VALUES (
842         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
843         'xin',
844         COALESCE(NEW.parenthesize, FALSE),
845         NEW.parent_expr,
846         COALESCE(NEW.seq_no, 1),
847                 NEW.left_operand,
848                 NEW.subquery,
849                 NEW.negate
850     );
851
852 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
853     ON UPDATE TO query.expr_xin
854     DO INSTEAD
855     UPDATE query.expression SET
856         id = NEW.id,
857         parenthesize = NEW.parenthesize,
858         parent_expr = NEW.parent_expr,
859         seq_no = NEW.seq_no,
860                 left_operand = NEW.left_operand,
861                 subquery = NEW.subquery,
862                 negate = NEW.negate
863     WHERE
864         id = OLD.id;
865
866 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
867     ON DELETE TO query.expr_xin
868     DO INSTEAD
869     DELETE FROM query.expression WHERE id = OLD.id;
870
871 -- Create updatable view for NULL expressions
872
873 CREATE OR REPLACE VIEW query.expr_xnull AS
874     SELECT
875                 id,
876                 parenthesize,
877                 parent_expr,
878                 seq_no,
879                 negate
880     FROM
881         query.expression
882     WHERE
883         type = 'xnull';
884
885 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
886     ON INSERT TO query.expr_xnull
887     DO INSTEAD
888     INSERT INTO query.expression (
889                 id,
890                 type,
891                 parenthesize,
892                 parent_expr,
893                 seq_no,
894                 negate
895     ) VALUES (
896         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
897         'xnull',
898         COALESCE(NEW.parenthesize, FALSE),
899         NEW.parent_expr,
900         COALESCE(NEW.seq_no, 1),
901                 NEW.negate
902     );
903
904 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
905     ON UPDATE TO query.expr_xnull
906     DO INSTEAD
907     UPDATE query.expression SET
908         id = NEW.id,
909         parenthesize = NEW.parenthesize,
910         parent_expr = NEW.parent_expr,
911         seq_no = NEW.seq_no,
912                 negate = NEW.negate
913     WHERE
914         id = OLD.id;
915
916 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
917     ON DELETE TO query.expr_xnull
918     DO INSTEAD
919     DELETE FROM query.expression WHERE id = OLD.id;
920
921 -- Create updatable view for numeric literal expressions
922
923 CREATE OR REPLACE VIEW query.expr_xnum AS
924     SELECT
925                 id,
926                 parenthesize,
927                 parent_expr,
928                 seq_no,
929                 literal
930     FROM
931         query.expression
932     WHERE
933         type = 'xnum';
934
935 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
936     ON INSERT TO query.expr_xnum
937     DO INSTEAD
938     INSERT INTO query.expression (
939                 id,
940                 type,
941                 parenthesize,
942                 parent_expr,
943                 seq_no,
944                 literal
945     ) VALUES (
946         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
947         'xnum',
948         COALESCE(NEW.parenthesize, FALSE),
949         NEW.parent_expr,
950         COALESCE(NEW.seq_no, 1),
951         NEW.literal
952     );
953
954 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
955     ON UPDATE TO query.expr_xnum
956     DO INSTEAD
957     UPDATE query.expression SET
958         id = NEW.id,
959         parenthesize = NEW.parenthesize,
960         parent_expr = NEW.parent_expr,
961         seq_no = NEW.seq_no,
962         literal = NEW.literal
963     WHERE
964         id = OLD.id;
965
966 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
967     ON DELETE TO query.expr_xnum
968     DO INSTEAD
969     DELETE FROM query.expression WHERE id = OLD.id;
970
971 -- Create updatable view for operator expressions
972
973 CREATE OR REPLACE VIEW query.expr_xop AS
974     SELECT
975                 id,
976                 parenthesize,
977                 parent_expr,
978                 seq_no,
979                 left_operand,
980                 operator,
981                 right_operand,
982                 negate
983     FROM
984         query.expression
985     WHERE
986         type = 'xop';
987
988 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
989     ON INSERT TO query.expr_xop
990     DO INSTEAD
991     INSERT INTO query.expression (
992                 id,
993                 type,
994                 parenthesize,
995                 parent_expr,
996                 seq_no,
997                 left_operand,
998                 operator,
999                 right_operand,
1000                 negate
1001     ) VALUES (
1002         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1003         'xop',
1004         COALESCE(NEW.parenthesize, FALSE),
1005         NEW.parent_expr,
1006         COALESCE(NEW.seq_no, 1),
1007                 NEW.left_operand,
1008                 NEW.operator,
1009                 NEW.right_operand,
1010                 NEW.negate
1011     );
1012
1013 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
1014     ON UPDATE TO query.expr_xop
1015     DO INSTEAD
1016     UPDATE query.expression SET
1017         id = NEW.id,
1018         parenthesize = NEW.parenthesize,
1019         parent_expr = NEW.parent_expr,
1020         seq_no = NEW.seq_no,
1021                 left_operand = NEW.left_operand,
1022                 operator = NEW.operator,
1023                 right_operand = NEW.right_operand,
1024                 negate = NEW.negate
1025     WHERE
1026         id = OLD.id;
1027
1028 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1029     ON DELETE TO query.expr_xop
1030     DO INSTEAD
1031     DELETE FROM query.expression WHERE id = OLD.id;
1032
1033 -- Create updatable view for string literal expressions
1034
1035 CREATE OR REPLACE VIEW query.expr_xstr AS
1036     SELECT
1037         id,
1038         parenthesize,
1039         parent_expr,
1040         seq_no,
1041         literal
1042     FROM
1043         query.expression
1044     WHERE
1045         type = 'xstr';
1046
1047 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1048     ON INSERT TO query.expr_xstr
1049     DO INSTEAD
1050     INSERT INTO query.expression (
1051         id,
1052         type,
1053         parenthesize,
1054         parent_expr,
1055         seq_no,
1056         literal
1057     ) VALUES (
1058         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1059         'xstr',
1060         COALESCE(NEW.parenthesize, FALSE),
1061         NEW.parent_expr,
1062         COALESCE(NEW.seq_no, 1),
1063         NEW.literal
1064     );
1065
1066 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1067     ON UPDATE TO query.expr_xstr
1068     DO INSTEAD
1069     UPDATE query.expression SET
1070         id = NEW.id,
1071         parenthesize = NEW.parenthesize,
1072         parent_expr = NEW.parent_expr,
1073         seq_no = NEW.seq_no,
1074         literal = NEW.literal
1075     WHERE
1076         id = OLD.id;
1077
1078 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1079     ON DELETE TO query.expr_xstr
1080     DO INSTEAD
1081     DELETE FROM query.expression WHERE id = OLD.id;
1082
1083 -- Create updatable view for subquery expressions
1084
1085 CREATE OR REPLACE VIEW query.expr_xsubq AS
1086     SELECT
1087                 id,
1088                 parenthesize,
1089                 parent_expr,
1090                 seq_no,
1091                 subquery,
1092                 negate
1093     FROM
1094         query.expression
1095     WHERE
1096         type = 'xsubq';
1097
1098 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1099     ON INSERT TO query.expr_xsubq
1100     DO INSTEAD
1101     INSERT INTO query.expression (
1102                 id,
1103                 type,
1104                 parenthesize,
1105                 parent_expr,
1106                 seq_no,
1107                 subquery,
1108                 negate
1109     ) VALUES (
1110         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1111         'xsubq',
1112         COALESCE(NEW.parenthesize, FALSE),
1113         NEW.parent_expr,
1114         COALESCE(NEW.seq_no, 1),
1115                 NEW.subquery,
1116                 NEW.negate
1117     );
1118
1119 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1120     ON UPDATE TO query.expr_xsubq
1121     DO INSTEAD
1122     UPDATE query.expression SET
1123         id = NEW.id,
1124         parenthesize = NEW.parenthesize,
1125         parent_expr = NEW.parent_expr,
1126         seq_no = NEW.seq_no,
1127                 subquery = NEW.subquery,
1128                 negate = NEW.negate
1129     WHERE
1130         id = OLD.id;
1131
1132 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1133     ON DELETE TO query.expr_xsubq
1134     DO INSTEAD
1135     DELETE FROM query.expression WHERE id = OLD.id;
1136
1137 COMMIT;