1-- 2-- CASE 3-- Test the case statement 4-- 5CREATE TABLE CASE_TBL ( 6 i integer, 7 f double precision 8); 9CREATE TABLE CASE2_TBL ( 10 i integer, 11 j integer 12); 13INSERT INTO CASE_TBL VALUES (1, 10.1); 14INSERT INTO CASE_TBL VALUES (2, 20.2); 15INSERT INTO CASE_TBL VALUES (3, -30.3); 16INSERT INTO CASE_TBL VALUES (4, NULL); 17INSERT INTO CASE2_TBL VALUES (1, -1); 18INSERT INTO CASE2_TBL VALUES (2, -2); 19INSERT INTO CASE2_TBL VALUES (3, -3); 20INSERT INTO CASE2_TBL VALUES (2, -4); 21INSERT INTO CASE2_TBL VALUES (1, NULL); 22INSERT INTO CASE2_TBL VALUES (NULL, -6); 23-- 24-- Simplest examples without tables 25-- 26SELECT '3' AS "One", 27 CASE 28 WHEN 1 < 2 THEN 3 29 END AS "Simple WHEN"; 30 One | Simple WHEN 31-----+------------- 32 3 | 3 33(1 row) 34 35SELECT '<NULL>' AS "One", 36 CASE 37 WHEN 1 > 2 THEN 3 38 END AS "Simple default"; 39 One | Simple default 40--------+---------------- 41 <NULL> | 42(1 row) 43 44SELECT '3' AS "One", 45 CASE 46 WHEN 1 < 2 THEN 3 47 ELSE 4 48 END AS "Simple ELSE"; 49 One | Simple ELSE 50-----+------------- 51 3 | 3 52(1 row) 53 54SELECT '4' AS "One", 55 CASE 56 WHEN 1 > 2 THEN 3 57 ELSE 4 58 END AS "ELSE default"; 59 One | ELSE default 60-----+-------------- 61 4 | 4 62(1 row) 63 64SELECT '6' AS "One", 65 CASE 66 WHEN 1 > 2 THEN 3 67 WHEN 4 < 5 THEN 6 68 ELSE 7 69 END AS "Two WHEN with default"; 70 One | Two WHEN with default 71-----+----------------------- 72 6 | 6 73(1 row) 74 75SELECT '7' AS "None", 76 CASE WHEN random() < 0 THEN 1 77 END AS "NULL on no matches"; 78 None | NULL on no matches 79------+-------------------- 80 7 | 81(1 row) 82 83-- Constant-expression folding shouldn't evaluate unreachable subexpressions 84SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END; 85 case 86------ 87 1 88(1 row) 89 90SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END; 91 case 92------ 93 1 94(1 row) 95 96-- However we do not currently suppress folding of potentially 97-- reachable subexpressions 98SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl; 99ERROR: division by zero 100-- Test for cases involving untyped literals in test expression 101SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END; 102 case 103------ 104 1 105(1 row) 106 107-- 108-- Examples of targets involving tables 109-- 110SELECT '' AS "Five", 111 CASE 112 WHEN i >= 3 THEN i 113 END AS ">= 3 or Null" 114 FROM CASE_TBL; 115 Five | >= 3 or Null 116------+-------------- 117 | 118 | 119 | 3 120 | 4 121(4 rows) 122 123SELECT '' AS "Five", 124 CASE WHEN i >= 3 THEN (i + i) 125 ELSE i 126 END AS "Simplest Math" 127 FROM CASE_TBL; 128 Five | Simplest Math 129------+--------------- 130 | 1 131 | 2 132 | 6 133 | 8 134(4 rows) 135 136SELECT '' AS "Five", i AS "Value", 137 CASE WHEN (i < 0) THEN 'small' 138 WHEN (i = 0) THEN 'zero' 139 WHEN (i = 1) THEN 'one' 140 WHEN (i = 2) THEN 'two' 141 ELSE 'big' 142 END AS "Category" 143 FROM CASE_TBL; 144 Five | Value | Category 145------+-------+---------- 146 | 1 | one 147 | 2 | two 148 | 3 | big 149 | 4 | big 150(4 rows) 151 152SELECT '' AS "Five", 153 CASE WHEN ((i < 0) or (i < 0)) THEN 'small' 154 WHEN ((i = 0) or (i = 0)) THEN 'zero' 155 WHEN ((i = 1) or (i = 1)) THEN 'one' 156 WHEN ((i = 2) or (i = 2)) THEN 'two' 157 ELSE 'big' 158 END AS "Category" 159 FROM CASE_TBL; 160 Five | Category 161------+---------- 162 | one 163 | two 164 | big 165 | big 166(4 rows) 167 168-- 169-- Examples of qualifications involving tables 170-- 171-- 172-- NULLIF() and COALESCE() 173-- Shorthand forms for typical CASE constructs 174-- defined in the SQL standard. 175-- 176SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4; 177 i | f 178---+--- 179 4 | 180(1 row) 181 182SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2; 183 i | f 184---+--- 185(0 rows) 186 187SELECT COALESCE(a.f, b.i, b.j) 188 FROM CASE_TBL a, CASE2_TBL b; 189 coalesce 190---------- 191 10.1 192 20.2 193 -30.3 194 1 195 10.1 196 20.2 197 -30.3 198 2 199 10.1 200 20.2 201 -30.3 202 3 203 10.1 204 20.2 205 -30.3 206 2 207 10.1 208 20.2 209 -30.3 210 1 211 10.1 212 20.2 213 -30.3 214 -6 215(24 rows) 216 217SELECT * 218 FROM CASE_TBL a, CASE2_TBL b 219 WHERE COALESCE(a.f, b.i, b.j) = 2; 220 i | f | i | j 221---+---+---+---- 222 4 | | 2 | -2 223 4 | | 2 | -4 224(2 rows) 225 226SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)", 227 NULLIF(b.i, 4) AS "NULLIF(b.i,4)" 228 FROM CASE_TBL a, CASE2_TBL b; 229 five | NULLIF(a.i,b.i) | NULLIF(b.i,4) 230------+-----------------+--------------- 231 | | 1 232 | 2 | 1 233 | 3 | 1 234 | 4 | 1 235 | 1 | 2 236 | | 2 237 | 3 | 2 238 | 4 | 2 239 | 1 | 3 240 | 2 | 3 241 | | 3 242 | 4 | 3 243 | 1 | 2 244 | | 2 245 | 3 | 2 246 | 4 | 2 247 | | 1 248 | 2 | 1 249 | 3 | 1 250 | 4 | 1 251 | 1 | 252 | 2 | 253 | 3 | 254 | 4 | 255(24 rows) 256 257SELECT '' AS "Two", * 258 FROM CASE_TBL a, CASE2_TBL b 259 WHERE COALESCE(f,b.i) = 2; 260 Two | i | f | i | j 261-----+---+---+---+---- 262 | 4 | | 2 | -2 263 | 4 | | 2 | -4 264(2 rows) 265 266-- 267-- Examples of updates involving tables 268-- 269UPDATE CASE_TBL 270 SET i = CASE WHEN i >= 3 THEN (- i) 271 ELSE (2 * i) END; 272SELECT * FROM CASE_TBL; 273 i | f 274----+------- 275 2 | 10.1 276 4 | 20.2 277 -3 | -30.3 278 -4 | 279(4 rows) 280 281UPDATE CASE_TBL 282 SET i = CASE WHEN i >= 2 THEN (2 * i) 283 ELSE (3 * i) END; 284SELECT * FROM CASE_TBL; 285 i | f 286-----+------- 287 4 | 10.1 288 8 | 20.2 289 -9 | -30.3 290 -12 | 291(4 rows) 292 293UPDATE CASE_TBL 294 SET i = CASE WHEN b.i >= 2 THEN (2 * j) 295 ELSE (3 * j) END 296 FROM CASE2_TBL b 297 WHERE j = -CASE_TBL.i; 298SELECT * FROM CASE_TBL; 299 i | f 300-----+------- 301 8 | 20.2 302 -9 | -30.3 303 -12 | 304 -8 | 10.1 305(4 rows) 306 307-- 308-- Nested CASE expressions 309-- 310-- This test exercises a bug caused by aliasing econtext->caseValue_isNull 311-- with the isNull argument of the inner CASE's CaseExpr evaluation. After 312-- evaluating the vol(null) expression in the inner CASE's second WHEN-clause, 313-- the isNull flag for the case test value incorrectly became true, causing 314-- the third WHEN-clause not to match. The volatile function calls are needed 315-- to prevent constant-folding in the planner, which would hide the bug. 316-- Wrap this in a single transaction so the transient '=' operator doesn't 317-- cause problems in concurrent sessions 318BEGIN; 319CREATE FUNCTION vol(text) returns text as 320 'begin return $1; end' language plpgsql volatile; 321SELECT CASE 322 (CASE vol('bar') 323 WHEN 'foo' THEN 'it was foo!' 324 WHEN vol(null) THEN 'null input' 325 WHEN 'bar' THEN 'it was bar!' END 326 ) 327 WHEN 'it was foo!' THEN 'foo recognized' 328 WHEN 'it was bar!' THEN 'bar recognized' 329 ELSE 'unrecognized' END; 330 case 331---------------- 332 bar recognized 333(1 row) 334 335-- In this case, we can't inline the SQL function without confusing things. 336CREATE DOMAIN foodomain AS text; 337CREATE FUNCTION volfoo(text) returns foodomain as 338 'begin return $1::foodomain; end' language plpgsql volatile; 339CREATE FUNCTION inline_eq(foodomain, foodomain) returns boolean as 340 'SELECT CASE $2::text WHEN $1::text THEN true ELSE false END' language sql; 341CREATE OPERATOR = (procedure = inline_eq, 342 leftarg = foodomain, rightarg = foodomain); 343SELECT CASE volfoo('bar') WHEN 'foo'::foodomain THEN 'is foo' ELSE 'is not foo' END; 344 case 345------------ 346 is not foo 347(1 row) 348 349ROLLBACK; 350-- Test multiple evaluation of a CASE arg that is a read/write object (#14472) 351-- Wrap this in a single transaction so the transient '=' operator doesn't 352-- cause problems in concurrent sessions 353BEGIN; 354CREATE DOMAIN arrdomain AS int[]; 355CREATE FUNCTION make_ad(int,int) returns arrdomain as 356 'declare x arrdomain; 357 begin 358 x := array[$1,$2]; 359 return x; 360 end' language plpgsql volatile; 361CREATE FUNCTION ad_eq(arrdomain, arrdomain) returns boolean as 362 'begin return array_eq($1, $2); end' language plpgsql; 363CREATE OPERATOR = (procedure = ad_eq, 364 leftarg = arrdomain, rightarg = arrdomain); 365SELECT CASE make_ad(1,2) 366 WHEN array[2,4]::arrdomain THEN 'wrong' 367 WHEN array[2,5]::arrdomain THEN 'still wrong' 368 WHEN array[1,2]::arrdomain THEN 'right' 369 END; 370 case 371------- 372 right 373(1 row) 374 375ROLLBACK; 376-- 377-- Clean up 378-- 379DROP TABLE CASE_TBL; 380DROP TABLE CASE2_TBL; 381