Open-ILS/src/sql/Pg/upgrade/0286.schema.query-coalesce-negate.sql

   1 BEGIN;
   2
   3 -- For various updatable views based on query.expression:
   4 -- add COALESCE to the insert rule so that we don't always
   5 -- have to specify a value for the "negate" column.
   6
   7 INSERT INTO config.upgrade_log (version) VALUES ('0286'); -- Scott McKellar
   8
   9 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
  10     ON INSERT TO query.expr_xbet
  11     DO INSTEAD
  12     INSERT INTO query.expression (
  13                 id,
  14                 type,
  15                 parenthesize,
  16                 parent_expr,
  17                 seq_no,
  18                 negate
  19     ) VALUES (
  20         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
  21         'xbet',
  22         COALESCE(NEW.parenthesize, FALSE),
  23         NEW.parent_expr,
  24         COALESCE(NEW.seq_no, 1),
  25                 COALESCE(NEW.negate, false)
  26     );
  27
  28 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
  29     ON INSERT TO query.expr_xbool
  30     DO INSTEAD
  31     INSERT INTO query.expression (
  32                 id,
  33                 type,
  34                 parenthesize,
  35                 parent_expr,
  36                 seq_no,
  37                 literal,
  38                 negate
  39     ) VALUES (
  40         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
  41         'xbool',
  42         COALESCE(NEW.parenthesize, FALSE),
  43         NEW.parent_expr,
  44         COALESCE(NEW.seq_no, 1),
  45         NEW.literal,
  46                 COALESCE(NEW.negate, false)
  47     );
  48
  49 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
  50     ON INSERT TO query.expr_xcase
  51     DO INSTEAD
  52     INSERT INTO query.expression (
  53                 id,
  54                 type,
  55                 parenthesize,
  56                 parent_expr,
  57                 seq_no,
  58                 negate
  59     ) VALUES (
  60         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
  61         'xcase',
  62         COALESCE(NEW.parenthesize, FALSE),
  63         NEW.parent_expr,
  64         COALESCE(NEW.seq_no, 1),
  65                 COALESCE(NEW.negate, false)
  66     );
  67
  68 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
  69     ON INSERT TO query.expr_xcast
  70     DO INSTEAD
  71     INSERT INTO query.expression (
  72                 id,
  73                 type,
  74                 parenthesize,
  75                 parent_expr,
  76                 seq_no,
  77                 left_operand,
  78                 cast_type,
  79                 negate
  80     ) VALUES (
  81         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
  82         'xcast',
  83         COALESCE(NEW.parenthesize, FALSE),
  84         NEW.parent_expr,
  85         COALESCE(NEW.seq_no, 1),
  86                 NEW.left_operand,
  87                 NEW.cast_type,
  88                 COALESCE(NEW.negate, false)
  89     );
  90
  91 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
  92     ON INSERT TO query.expr_xcol
  93     DO INSTEAD
  94     INSERT INTO query.expression (
  95                 id,
  96                 type,
  97                 parenthesize,
  98                 parent_expr,
  99                 seq_no,
 100                 table_alias,
 101                 column_name,
 102                 negate
 103     ) VALUES (
 104         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 105         'xcol',
 106         COALESCE(NEW.parenthesize, FALSE),
 107         NEW.parent_expr,
 108         COALESCE(NEW.seq_no, 1),
 109                 NEW.table_alias,
 110                 NEW.column_name,
 111                 COALESCE(NEW.negate, false)
 112     );
 113
 114 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
 115     ON INSERT TO query.expr_xex
 116     DO INSTEAD
 117     INSERT INTO query.expression (
 118                 id,
 119                 type,
 120                 parenthesize,
 121                 parent_expr,
 122                 seq_no,
 123                 subquery,
 124                 negate
 125     ) VALUES (
 126         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 127         'xex',
 128         COALESCE(NEW.parenthesize, FALSE),
 129         NEW.parent_expr,
 130         COALESCE(NEW.seq_no, 1),
 131                 NEW.subquery,
 132                 COALESCE(NEW.negate, false)
 133     );
 134
 135 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
 136     ON INSERT TO query.expr_xfld
 137     DO INSTEAD
 138     INSERT INTO query.expression (
 139                 id,
 140                 type,
 141                 parenthesize,
 142                 parent_expr,
 143                 seq_no,
 144                 column_name,
 145                 left_operand,
 146                 negate
 147     ) VALUES (
 148         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 149         'xfld',
 150         COALESCE(NEW.parenthesize, FALSE),
 151         NEW.parent_expr,
 152         COALESCE(NEW.seq_no, 1),
 153                 NEW.column_name,
 154                 NEW.left_operand,
 155                 COALESCE(NEW.negate, false)
 156     );
 157
 158 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
 159     ON INSERT TO query.expr_xfunc
 160     DO INSTEAD
 161     INSERT INTO query.expression (
 162                 id,
 163                 type,
 164                 parenthesize,
 165                 parent_expr,
 166                 seq_no,
 167                 function_id,
 168                 negate
 169     ) VALUES (
 170         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 171         'xfunc',
 172         COALESCE(NEW.parenthesize, FALSE),
 173         NEW.parent_expr,
 174         COALESCE(NEW.seq_no, 1),
 175                 NEW.function_id,
 176                 COALESCE(NEW.negate, false)
 177     );
 178
 179 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
 180     ON INSERT TO query.expr_xin
 181     DO INSTEAD
 182     INSERT INTO query.expression (
 183                 id,
 184                 type,
 185                 parenthesize,
 186                 parent_expr,
 187                 seq_no,
 188                 left_operand,
 189                 subquery,
 190                 negate
 191     ) VALUES (
 192         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 193         'xin',
 194         COALESCE(NEW.parenthesize, FALSE),
 195         NEW.parent_expr,
 196         COALESCE(NEW.seq_no, 1),
 197                 NEW.left_operand,
 198                 NEW.subquery,
 199                 COALESCE(NEW.negate, false)
 200     );
 201
 202 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
 203     ON INSERT TO query.expr_xisnull
 204     DO INSTEAD
 205     INSERT INTO query.expression (
 206                 id,
 207                 type,
 208                 parenthesize,
 209                 parent_expr,
 210                 seq_no,
 211                 left_operand,
 212                 negate
 213     ) VALUES (
 214         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 215         'xisnull',
 216         COALESCE(NEW.parenthesize, FALSE),
 217         NEW.parent_expr,
 218         COALESCE(NEW.seq_no, 1),
 219                 NEW.left_operand,
 220                 COALESCE(NEW.negate, false)
 221     );
 222
 223 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
 224     ON INSERT TO query.expr_xnull
 225     DO INSTEAD
 226     INSERT INTO query.expression (
 227                 id,
 228                 type,
 229                 parenthesize,
 230                 parent_expr,
 231                 seq_no,
 232                 negate
 233     ) VALUES (
 234         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 235         'xnull',
 236         COALESCE(NEW.parenthesize, FALSE),
 237         NEW.parent_expr,
 238         COALESCE(NEW.seq_no, 1),
 239                 COALESCE(NEW.negate, false)
 240     );
 241
 242 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
 243     ON INSERT TO query.expr_xop
 244     DO INSTEAD
 245     INSERT INTO query.expression (
 246                 id,
 247                 type,
 248                 parenthesize,
 249                 parent_expr,
 250                 seq_no,
 251                 left_operand,
 252                 operator,
 253                 right_operand,
 254                 negate
 255     ) VALUES (
 256         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 257         'xop',
 258         COALESCE(NEW.parenthesize, FALSE),
 259         NEW.parent_expr,
 260         COALESCE(NEW.seq_no, 1),
 261                 NEW.left_operand,
 262                 NEW.operator,
 263                 NEW.right_operand,
 264                 COALESCE(NEW.negate, false)
 265     );
 266
 267 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
 268     ON INSERT TO query.expr_xser
 269     DO INSTEAD
 270     INSERT INTO query.expression (
 271                 id,
 272                 type,
 273                 parenthesize,
 274                 parent_expr,
 275                 seq_no,
 276                 operator,
 277                 negate
 278     ) VALUES (
 279         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 280         'xser',
 281         COALESCE(NEW.parenthesize, FALSE),
 282         NEW.parent_expr,
 283         COALESCE(NEW.seq_no, 1),
 284                 NEW.operator,
 285                 COALESCE(NEW.negate, false)
 286     );
 287
 288 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
 289     ON INSERT TO query.expr_xsubq
 290     DO INSTEAD
 291     INSERT INTO query.expression (
 292                 id,
 293                 type,
 294                 parenthesize,
 295                 parent_expr,
 296                 seq_no,
 297                 subquery,
 298                 negate
 299     ) VALUES (
 300         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 301         'xsubq',
 302         COALESCE(NEW.parenthesize, FALSE),
 303         NEW.parent_expr,
 304         COALESCE(NEW.seq_no, 1),
 305                 NEW.subquery,
 306                 COALESCE(NEW.negate, false)
 307     );
 308
 309 COMMIT;