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