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

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