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