Open-ILS/src/sql/Pg/008.schema.query.sql

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