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