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 62SELECT '7' AS "None", 63 CASE WHEN random() < 0 THEN 1 64 END AS "NULL on no matches"; 65 66-- Constant-expression folding shouldn't evaluate unreachable subexpressions 67SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END; 68SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END; 69 70-- However we do not currently suppress folding of potentially 71-- reachable subexpressions 72SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl; 73 74-- Test for cases involving untyped literals in test expression 75SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END; 76 77-- 78-- Examples of targets involving tables 79-- 80 81SELECT '' AS "Five", 82 CASE 83 WHEN i >= 3 THEN i 84 END AS ">= 3 or Null" 85 FROM CASE_TBL; 86 87SELECT '' AS "Five", 88 CASE WHEN i >= 3 THEN (i + i) 89 ELSE i 90 END AS "Simplest Math" 91 FROM CASE_TBL; 92 93SELECT '' AS "Five", i AS "Value", 94 CASE WHEN (i < 0) THEN 'small' 95 WHEN (i = 0) THEN 'zero' 96 WHEN (i = 1) THEN 'one' 97 WHEN (i = 2) THEN 'two' 98 ELSE 'big' 99 END AS "Category" 100 FROM CASE_TBL; 101 102SELECT '' AS "Five", 103 CASE WHEN ((i < 0) or (i < 0)) THEN 'small' 104 WHEN ((i = 0) or (i = 0)) THEN 'zero' 105 WHEN ((i = 1) or (i = 1)) THEN 'one' 106 WHEN ((i = 2) or (i = 2)) THEN 'two' 107 ELSE 'big' 108 END AS "Category" 109 FROM CASE_TBL; 110 111-- 112-- Examples of qualifications involving tables 113-- 114 115-- 116-- NULLIF() and COALESCE() 117-- Shorthand forms for typical CASE constructs 118-- defined in the SQL standard. 119-- 120 121SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4; 122 123SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2; 124 125SELECT COALESCE(a.f, b.i, b.j) 126 FROM CASE_TBL a, CASE2_TBL b; 127 128SELECT * 129 FROM CASE_TBL a, CASE2_TBL b 130 WHERE COALESCE(a.f, b.i, b.j) = 2; 131 132SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)", 133 NULLIF(b.i, 4) AS "NULLIF(b.i,4)" 134 FROM CASE_TBL a, CASE2_TBL b; 135 136SELECT '' AS "Two", * 137 FROM CASE_TBL a, CASE2_TBL b 138 WHERE COALESCE(f,b.i) = 2; 139 140-- 141-- Examples of updates involving tables 142-- 143 144UPDATE CASE_TBL 145 SET i = CASE WHEN i >= 3 THEN (- i) 146 ELSE (2 * i) END; 147 148SELECT * FROM CASE_TBL; 149 150UPDATE CASE_TBL 151 SET i = CASE WHEN i >= 2 THEN (2 * i) 152 ELSE (3 * i) END; 153 154SELECT * FROM CASE_TBL; 155 156UPDATE CASE_TBL 157 SET i = CASE WHEN b.i >= 2 THEN (2 * j) 158 ELSE (3 * j) END 159 FROM CASE2_TBL b 160 WHERE j = -CASE_TBL.i; 161 162SELECT * FROM CASE_TBL; 163 164-- 165-- Nested CASE expressions 166-- 167 168-- This test exercises a bug caused by aliasing econtext->caseValue_isNull 169-- with the isNull argument of the inner CASE's CaseExpr evaluation. After 170-- evaluating the vol(null) expression in the inner CASE's second WHEN-clause, 171-- the isNull flag for the case test value incorrectly became true, causing 172-- the third WHEN-clause not to match. The volatile function calls are needed 173-- to prevent constant-folding in the planner, which would hide the bug. 174 175-- Wrap this in a single transaction so the transient '=' operator doesn't 176-- cause problems in concurrent sessions 177BEGIN; 178 179CREATE FUNCTION vol(text) returns text as 180 'begin return $1; end' language plpgsql volatile; 181 182SELECT CASE 183 (CASE vol('bar') 184 WHEN 'foo' THEN 'it was foo!' 185 WHEN vol(null) THEN 'null input' 186 WHEN 'bar' THEN 'it was bar!' END 187 ) 188 WHEN 'it was foo!' THEN 'foo recognized' 189 WHEN 'it was bar!' THEN 'bar recognized' 190 ELSE 'unrecognized' END; 191 192-- In this case, we can't inline the SQL function without confusing things. 193CREATE DOMAIN foodomain AS text; 194 195CREATE FUNCTION volfoo(text) returns foodomain as 196 'begin return $1::foodomain; end' language plpgsql volatile; 197 198CREATE FUNCTION inline_eq(foodomain, foodomain) returns boolean as 199 'SELECT CASE $2::text WHEN $1::text THEN true ELSE false END' language sql; 200 201CREATE OPERATOR = (procedure = inline_eq, 202 leftarg = foodomain, rightarg = foodomain); 203 204SELECT CASE volfoo('bar') WHEN 'foo'::foodomain THEN 'is foo' ELSE 'is not foo' END; 205 206ROLLBACK; 207 208-- Test multiple evaluation of a CASE arg that is a read/write object (#14472) 209-- Wrap this in a single transaction so the transient '=' operator doesn't 210-- cause problems in concurrent sessions 211BEGIN; 212 213CREATE DOMAIN arrdomain AS int[]; 214 215CREATE FUNCTION make_ad(int,int) returns arrdomain as 216 'declare x arrdomain; 217 begin 218 x := array[$1,$2]; 219 return x; 220 end' language plpgsql volatile; 221 222CREATE FUNCTION ad_eq(arrdomain, arrdomain) returns boolean as 223 'begin return array_eq($1, $2); end' language plpgsql; 224 225CREATE OPERATOR = (procedure = ad_eq, 226 leftarg = arrdomain, rightarg = arrdomain); 227 228SELECT CASE make_ad(1,2) 229 WHEN array[2,4]::arrdomain THEN 'wrong' 230 WHEN array[2,5]::arrdomain THEN 'still wrong' 231 WHEN array[1,2]::arrdomain THEN 'right' 232 END; 233 234ROLLBACK; 235 236-- Test interaction of CASE with ArrayCoerceExpr (bug #15471) 237BEGIN; 238 239CREATE TYPE casetestenum AS ENUM ('e', 'f', 'g'); 240 241SELECT 242 CASE 'foo'::text 243 WHEN 'foo' THEN ARRAY['a', 'b', 'c', 'd'] || enum_range(NULL::casetestenum)::text[] 244 ELSE ARRAY['x', 'y'] 245 END; 246 247ROLLBACK; 248 249-- 250-- Clean up 251-- 252 253DROP TABLE CASE_TBL; 254DROP TABLE CASE2_TBL; 255