1-- 2-- CASE 3-- Test the case statement 4-- 5 6CREATE TABLE CASE_TBL ( 7 i integer, 8 f double precision 9); 10 11CREATE TABLE CASE2_TBL ( 12 i integer, 13 j integer 14); 15 16INSERT INTO CASE_TBL VALUES (1, 10.1); 17INSERT INTO CASE_TBL VALUES (2, 20.2); 18INSERT INTO CASE_TBL VALUES (3, -30.3); 19INSERT INTO CASE_TBL VALUES (4, NULL); 20 21INSERT INTO CASE2_TBL VALUES (1, -1); 22INSERT INTO CASE2_TBL VALUES (2, -2); 23INSERT INTO CASE2_TBL VALUES (3, -3); 24INSERT INTO CASE2_TBL VALUES (2, -4); 25INSERT INTO CASE2_TBL VALUES (1, NULL); 26INSERT INTO CASE2_TBL VALUES (NULL, -6); 27 28-- 29-- Simplest examples without tables 30-- 31 32SELECT '3' AS "One", 33 CASE 34 WHEN 1 < 2 THEN 3 35 END AS "Simple WHEN"; 36 37SELECT '<NULL>' AS "One", 38 CASE 39 WHEN 1 > 2 THEN 3 40 END AS "Simple default"; 41 42SELECT '3' AS "One", 43 CASE 44 WHEN 1 < 2 THEN 3 45 ELSE 4 46 END AS "Simple ELSE"; 47 48SELECT '4' AS "One", 49 CASE 50 WHEN 1 > 2 THEN 3 51 ELSE 4 52 END AS "ELSE default"; 53 54SELECT '6' AS "One", 55 CASE 56 WHEN 1 > 2 THEN 3 57 WHEN 4 < 5 THEN 6 58 ELSE 7 59 END AS "Two WHEN with default"; 60 61-- Constant-expression folding shouldn't evaluate unreachable subexpressions 62SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END; 63SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END; 64 65-- However we do not currently suppress folding of potentially 66-- reachable subexpressions 67SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl; 68 69-- Test for cases involving untyped literals in test expression 70SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END; 71 72-- 73-- Examples of targets involving tables 74-- 75 76SELECT '' AS "Five", 77 CASE 78 WHEN i >= 3 THEN i 79 END AS ">= 3 or Null" 80 FROM CASE_TBL; 81 82SELECT '' AS "Five", 83 CASE WHEN i >= 3 THEN (i + i) 84 ELSE i 85 END AS "Simplest Math" 86 FROM CASE_TBL; 87 88SELECT '' AS "Five", i AS "Value", 89 CASE WHEN (i < 0) THEN 'small' 90 WHEN (i = 0) THEN 'zero' 91 WHEN (i = 1) THEN 'one' 92 WHEN (i = 2) THEN 'two' 93 ELSE 'big' 94 END AS "Category" 95 FROM CASE_TBL; 96 97SELECT '' AS "Five", 98 CASE WHEN ((i < 0) or (i < 0)) THEN 'small' 99 WHEN ((i = 0) or (i = 0)) THEN 'zero' 100 WHEN ((i = 1) or (i = 1)) THEN 'one' 101 WHEN ((i = 2) or (i = 2)) THEN 'two' 102 ELSE 'big' 103 END AS "Category" 104 FROM CASE_TBL; 105 106-- 107-- Examples of qualifications involving tables 108-- 109 110-- 111-- NULLIF() and COALESCE() 112-- Shorthand forms for typical CASE constructs 113-- defined in the SQL standard. 114-- 115 116SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4; 117 118SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2; 119 120SELECT COALESCE(a.f, b.i, b.j) 121 FROM CASE_TBL a, CASE2_TBL b; 122 123SELECT * 124 FROM CASE_TBL a, CASE2_TBL b 125 WHERE COALESCE(a.f, b.i, b.j) = 2; 126 127SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)", 128 NULLIF(b.i, 4) AS "NULLIF(b.i,4)" 129 FROM CASE_TBL a, CASE2_TBL b; 130 131SELECT '' AS "Two", * 132 FROM CASE_TBL a, CASE2_TBL b 133 WHERE COALESCE(f,b.i) = 2; 134 135-- 136-- Examples of updates involving tables 137-- 138 139UPDATE CASE_TBL 140 SET i = CASE WHEN i >= 3 THEN (- i) 141 ELSE (2 * i) END; 142 143SELECT * FROM CASE_TBL; 144 145UPDATE CASE_TBL 146 SET i = CASE WHEN i >= 2 THEN (2 * i) 147 ELSE (3 * i) END; 148 149SELECT * FROM CASE_TBL; 150 151UPDATE CASE_TBL 152 SET i = CASE WHEN b.i >= 2 THEN (2 * j) 153 ELSE (3 * j) END 154 FROM CASE2_TBL b 155 WHERE j = -CASE_TBL.i; 156 157SELECT * FROM CASE_TBL; 158 159-- 160-- Nested CASE expressions 161-- 162 163-- This test exercises a bug caused by aliasing econtext->caseValue_isNull 164-- with the isNull argument of the inner CASE's ExecEvalCase() call. After 165-- evaluating the vol(null) expression in the inner CASE's second WHEN-clause, 166-- the isNull flag for the case test value incorrectly became true, causing 167-- the third WHEN-clause not to match. The volatile function calls are needed 168-- to prevent constant-folding in the planner, which would hide the bug. 169 170-- Wrap this in a single transaction so the transient '=' operator doesn't 171-- cause problems in concurrent sessions 172BEGIN; 173 174CREATE FUNCTION vol(text) returns text as 175 'begin return $1; end' language plpgsql volatile; 176 177SELECT CASE 178 (CASE vol('bar') 179 WHEN 'foo' THEN 'it was foo!' 180 WHEN vol(null) THEN 'null input' 181 WHEN 'bar' THEN 'it was bar!' END 182 ) 183 WHEN 'it was foo!' THEN 'foo recognized' 184 WHEN 'it was bar!' THEN 'bar recognized' 185 ELSE 'unrecognized' END; 186 187-- In this case, we can't inline the SQL function without confusing things. 188CREATE DOMAIN foodomain AS text; 189 190CREATE FUNCTION volfoo(text) returns foodomain as 191 'begin return $1::foodomain; end' language plpgsql volatile; 192 193CREATE FUNCTION inline_eq(foodomain, foodomain) returns boolean as 194 'SELECT CASE $2::text WHEN $1::text THEN true ELSE false END' language sql; 195 196CREATE OPERATOR = (procedure = inline_eq, 197 leftarg = foodomain, rightarg = foodomain); 198 199SELECT CASE volfoo('bar') WHEN 'foo'::foodomain THEN 'is foo' ELSE 'is not foo' END; 200 201ROLLBACK; 202 203-- Test multiple evaluation of a CASE arg that is a read/write object (#14472) 204-- Wrap this in a single transaction so the transient '=' operator doesn't 205-- cause problems in concurrent sessions 206BEGIN; 207 208CREATE DOMAIN arrdomain AS int[]; 209 210CREATE FUNCTION make_ad(int,int) returns arrdomain as 211 'declare x arrdomain; 212 begin 213 x := array[$1,$2]; 214 return x; 215 end' language plpgsql volatile; 216 217CREATE FUNCTION ad_eq(arrdomain, arrdomain) returns boolean as 218 'begin return array_eq($1, $2); end' language plpgsql; 219 220CREATE OPERATOR = (procedure = ad_eq, 221 leftarg = arrdomain, rightarg = arrdomain); 222 223SELECT CASE make_ad(1,2) 224 WHEN array[2,4]::arrdomain THEN 'wrong' 225 WHEN array[2,5]::arrdomain THEN 'still wrong' 226 WHEN array[1,2]::arrdomain THEN 'right' 227 END; 228 229ROLLBACK; 230 231-- 232-- Clean up 233-- 234 235DROP TABLE CASE_TBL; 236DROP TABLE CASE2_TBL; 237