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