src/test/regress/sql/case.sql

   1 --
   2 -- CASE
   3 -- Test the case statement
   4 --
   5
   6 CREATE TABLE CASE_TBL (
   7   i integer,
   8   f double precision
   9 );
  10
  11 CREATE TABLE CASE2_TBL (
  12   i integer,
  13   j integer
  14 );
  15
  16 INSERT INTO CASE_TBL VALUES (1, 10.1);
  17 INSERT INTO CASE_TBL VALUES (2, 20.2);
  18 INSERT INTO CASE_TBL VALUES (3, -30.3);
  19 INSERT INTO CASE_TBL VALUES (4, NULL);
  20
  21 INSERT INTO CASE2_TBL VALUES (1, -1);
  22 INSERT INTO CASE2_TBL VALUES (2, -2);
  23 INSERT INTO CASE2_TBL VALUES (3, -3);
  24 INSERT INTO CASE2_TBL VALUES (2, -4);
  25 INSERT INTO CASE2_TBL VALUES (1, NULL);
  26 INSERT INTO CASE2_TBL VALUES (NULL, -6);
  27
  28 --
  29 -- Simplest examples without tables
  30 --
  31
  32 SELECT '3' AS "One",
  33   CASE
  34     WHEN 1 < 2 THEN 3
  35   END AS "Simple WHEN";
  36
  37 SELECT '<NULL>' AS "One",
  38   CASE
  39     WHEN 1 > 2 THEN 3
  40   END AS "Simple default";
  41
  42 SELECT '3' AS "One",
  43   CASE
  44     WHEN 1 < 2 THEN 3
  45     ELSE 4
  46   END AS "Simple ELSE";
  47
  48 SELECT '4' AS "One",
  49   CASE
  50     WHEN 1 > 2 THEN 3
  51     ELSE 4
  52   END AS "ELSE default";
  53
  54 SELECT '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
  62 SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END;
  63 SELECT 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
  67 SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl;
  68
  69 -- Test for cases involving untyped literals in test expression
  70 SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END;
  71
  72 --
  73 -- Examples of targets involving tables
  74 --
  75
  76 SELECT '' AS "Five",
  77   CASE
  78     WHEN i >= 3 THEN i
  79   END AS ">= 3 or Null"
  80   FROM CASE_TBL;
  81
  82 SELECT '' AS "Five",
  83   CASE WHEN i >= 3 THEN (i + i)
  84        ELSE i
  85   END AS "Simplest Math"
  86   FROM CASE_TBL;
  87
  88 SELECT '' 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
  97 SELECT '' 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 SQL92 standard.
 114 --
 115
 116 SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4;
 117
 118 SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2;
 119
 120 SELECT COALESCE(a.f, b.i, b.j)
 121   FROM CASE_TBL a, CASE2_TBL b;
 122
 123 SELECT *
 124   FROM CASE_TBL a, CASE2_TBL b
 125   WHERE COALESCE(a.f, b.i, b.j) = 2;
 126
 127 SELECT '' 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
 131 SELECT '' 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
 139 UPDATE CASE_TBL
 140   SET i = CASE WHEN i >= 3 THEN (- i)
 141                 ELSE (2 * i) END;
 142
 143 SELECT * FROM CASE_TBL;
 144
 145 UPDATE CASE_TBL
 146   SET i = CASE WHEN i >= 2 THEN (2 * i)
 147                 ELSE (3 * i) END;
 148
 149 SELECT * FROM CASE_TBL;
 150
 151 UPDATE 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
 157 SELECT * FROM CASE_TBL;
 158
 159 --
 160 -- Clean up
 161 --
 162
 163 DROP TABLE CASE_TBL;
 164 DROP TABLE CASE2_TBL;
 165