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