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