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