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