3
-- Test the case statement
5
CREATE TABLE CASE_TBL (
9
CREATE TABLE CASE2_TBL (
13
INSERT INTO CASE_TBL VALUES (1, 10.1);
14
INSERT INTO CASE_TBL VALUES (2, 20.2);
15
INSERT INTO CASE_TBL VALUES (3, -30.3);
16
INSERT INTO CASE_TBL VALUES (4, NULL);
17
INSERT INTO CASE2_TBL VALUES (1, -1);
18
INSERT INTO CASE2_TBL VALUES (2, -2);
19
INSERT INTO CASE2_TBL VALUES (3, -3);
20
INSERT INTO CASE2_TBL VALUES (2, -4);
21
INSERT INTO CASE2_TBL VALUES (1, NULL);
22
INSERT INTO CASE2_TBL VALUES (NULL, -6);
24
-- Simplest examples without tables
35
SELECT '<NULL>' AS "One",
38
END AS "Simple default";
40
--------+----------------
58
END AS "ELSE default";
69
END AS "Two WHEN with default";
70
One | Two WHEN with default
71
-----+-----------------------
75
-- Constant-expression folding shouldn't evaluate unreachable subexpressions
76
SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END;
82
SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END;
88
-- However we do not currently suppress folding of potentially
89
-- reachable subexpressions
90
SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl;
91
ERROR: division by zero
92
-- Test for cases involving untyped literals in test expression
93
SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END;
100
-- Examples of targets involving tables
105
END AS ">= 3 or Null"
108
------+--------------
116
CASE WHEN i >= 3 THEN (i + i)
118
END AS "Simplest Math"
121
------+---------------
128
SELECT '' AS "Five", i AS "Value",
129
CASE WHEN (i < 0) THEN 'small'
130
WHEN (i = 0) THEN 'zero'
131
WHEN (i = 1) THEN 'one'
132
WHEN (i = 2) THEN 'two'
136
Five | Value | Category
137
------+-------+----------
145
CASE WHEN ((i < 0) or (i < 0)) THEN 'small'
146
WHEN ((i = 0) or (i = 0)) THEN 'zero'
147
WHEN ((i = 1) or (i = 1)) THEN 'one'
148
WHEN ((i = 2) or (i = 2)) THEN 'two'
161
-- Examples of qualifications involving tables
164
-- NULLIF() and COALESCE()
165
-- Shorthand forms for typical CASE constructs
166
-- defined in the SQL standard.
168
SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4;
174
SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2;
179
SELECT COALESCE(a.f, b.i, b.j)
180
FROM CASE_TBL a, CASE2_TBL b;
210
FROM CASE_TBL a, CASE2_TBL b
211
WHERE COALESCE(a.f, b.i, b.j) = 2;
218
SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)",
219
NULLIF(b.i, 4) AS "NULLIF(b.i,4)"
220
FROM CASE_TBL a, CASE2_TBL b;
221
five | NULLIF(a.i,b.i) | NULLIF(b.i,4)
222
------+-----------------+---------------
249
SELECT '' AS "Two", *
250
FROM CASE_TBL a, CASE2_TBL b
251
WHERE COALESCE(f,b.i) = 2;
253
-----+---+---+---+----
259
-- Examples of updates involving tables
262
SET i = CASE WHEN i >= 3 THEN (- i)
264
SELECT * FROM CASE_TBL;
274
SET i = CASE WHEN i >= 2 THEN (2 * i)
276
SELECT * FROM CASE_TBL;
286
SET i = CASE WHEN b.i >= 2 THEN (2 * j)
289
WHERE j = -CASE_TBL.i;
290
SELECT * FROM CASE_TBL;
303
DROP TABLE CASE2_TBL;