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