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