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