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

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