conversion.sql 9.5 KB
Newer Older
Tatsuo Ishii's avatar
Tatsuo Ishii committed
1 2 3
--
-- create user defined conversion
--
4 5
CREATE USER conversion_test_user WITH NOCREATEDB NOCREATEUSER;
SET SESSION AUTHORIZATION conversion_test_user;
6
CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
Tatsuo Ishii's avatar
Tatsuo Ishii committed
7 8 9
--
-- cannot make same name conversion in same schema
--
10
CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
Tatsuo Ishii's avatar
Tatsuo Ishii committed
11 12 13
--
-- create default conversion with qualified name
--
14
CREATE DEFAULT CONVERSION public.mydef FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
Tatsuo Ishii's avatar
Tatsuo Ishii committed
15 16 17
--
-- cannot make default conversion with same shcema/for_encoding/to_encoding
--
18
CREATE DEFAULT CONVERSION public.mydef2 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
19 20 21 22
-- test comments
COMMENT ON CONVERSION myconv_bad IS 'foo';
COMMENT ON CONVERSION myconv IS 'bar';
COMMENT ON CONVERSION myconv IS NULL;
Tatsuo Ishii's avatar
Tatsuo Ishii committed
23 24 25 26 27 28 29
--
-- drop user defined conversion
--
DROP CONVERSION myconv;
DROP CONVERSION mydef;
--
-- make sure all pre-defined conversions are fine.
30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
-- SQL_ASCII --> MULE_INTERNAL
SELECT CONVERT('foo', 'SQL_ASCII', 'MULE_INTERNAL');
-- MULE_INTERNAL --> SQL_ASCII
SELECT CONVERT('foo', 'MULE_INTERNAL', 'SQL_ASCII');
-- KOI8R --> MULE_INTERNAL
SELECT CONVERT('foo', 'KOI8R', 'MULE_INTERNAL');
-- MULE_INTERNAL --> KOI8R
SELECT CONVERT('foo', 'MULE_INTERNAL', 'KOI8R');
-- ISO-8859-5 --> MULE_INTERNAL
SELECT CONVERT('foo', 'ISO-8859-5', 'MULE_INTERNAL');
-- MULE_INTERNAL --> ISO-8859-5
SELECT CONVERT('foo', 'MULE_INTERNAL', 'ISO-8859-5');
-- WIN1251 --> MULE_INTERNAL
SELECT CONVERT('foo', 'WIN1251', 'MULE_INTERNAL');
-- MULE_INTERNAL --> WIN1251
SELECT CONVERT('foo', 'MULE_INTERNAL', 'WIN1251');
46 47 48 49
-- WIN866 --> MULE_INTERNAL
SELECT CONVERT('foo', 'WIN866', 'MULE_INTERNAL');
-- MULE_INTERNAL --> WIN866
SELECT CONVERT('foo', 'MULE_INTERNAL', 'WIN866');
50 51 52 53
-- KOI8R --> WIN1251
SELECT CONVERT('foo', 'KOI8R', 'WIN1251');
-- WIN1251 --> KOI8R
SELECT CONVERT('foo', 'WIN1251', 'KOI8R');
54 55 56 57 58 59 60 61
-- KOI8R --> WIN866
SELECT CONVERT('foo', 'KOI8R', 'WIN866');
-- WIN866 --> KOI8R
SELECT CONVERT('foo', 'WIN866', 'KOI8R');
-- WIN866 --> WIN1251
SELECT CONVERT('foo', 'WIN866', 'WIN1251');
-- WIN1251 --> WIN866
SELECT CONVERT('foo', 'WIN1251', 'WIN866');
62 63 64 65 66 67 68 69
-- ISO-8859-5 --> KOI8R
SELECT CONVERT('foo', 'ISO-8859-5', 'KOI8R');
-- KOI8R --> ISO-8859-5
SELECT CONVERT('foo', 'KOI8R', 'ISO-8859-5');
-- ISO-8859-5 --> WIN1251
SELECT CONVERT('foo', 'ISO-8859-5', 'WIN1251');
-- WIN1251 --> ISO-8859-5
SELECT CONVERT('foo', 'WIN1251', 'ISO-8859-5');
70 71 72 73
-- ISO-8859-5 --> WIN866
SELECT CONVERT('foo', 'ISO-8859-5', 'WIN866');
-- WIN866 --> ISO-8859-5
SELECT CONVERT('foo', 'WIN866', 'ISO-8859-5');
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129
-- EUC_CN --> MULE_INTERNAL
SELECT CONVERT('foo', 'EUC_CN', 'MULE_INTERNAL');
-- MULE_INTERNAL --> EUC_CN
SELECT CONVERT('foo', 'MULE_INTERNAL', 'EUC_CN');
-- EUC_JP --> SJIS
SELECT CONVERT('foo', 'EUC_JP', 'SJIS');
-- SJIS --> EUC_JP
SELECT CONVERT('foo', 'SJIS', 'EUC_JP');
-- EUC_JP --> MULE_INTERNAL
SELECT CONVERT('foo', 'EUC_JP', 'MULE_INTERNAL');
-- SJIS --> MULE_INTERNAL
SELECT CONVERT('foo', 'SJIS', 'MULE_INTERNAL');
-- MULE_INTERNAL --> EUC_JP
SELECT CONVERT('foo', 'MULE_INTERNAL', 'EUC_JP');
-- MULE_INTERNAL --> SJIS
SELECT CONVERT('foo', 'MULE_INTERNAL', 'SJIS');
-- EUC_KR --> MULE_INTERNAL
SELECT CONVERT('foo', 'EUC_KR', 'MULE_INTERNAL');
-- MULE_INTERNAL --> EUC_KR
SELECT CONVERT('foo', 'MULE_INTERNAL', 'EUC_KR');
-- EUC_TW --> BIG5
SELECT CONVERT('foo', 'EUC_TW', 'BIG5');
-- BIG5 --> EUC_TW
SELECT CONVERT('foo', 'BIG5', 'EUC_TW');
-- EUC_TW --> MULE_INTERNAL
SELECT CONVERT('foo', 'EUC_TW', 'MULE_INTERNAL');
-- BIG5 --> MULE_INTERNAL
SELECT CONVERT('foo', 'BIG5', 'MULE_INTERNAL');
-- MULE_INTERNAL --> EUC_TW
SELECT CONVERT('foo', 'MULE_INTERNAL', 'EUC_TW');
-- MULE_INTERNAL --> BIG5
SELECT CONVERT('foo', 'MULE_INTERNAL', 'BIG5');
-- LATIN2 --> MULE_INTERNAL
SELECT CONVERT('foo', 'LATIN2', 'MULE_INTERNAL');
-- MULE_INTERNAL --> LATIN2
SELECT CONVERT('foo', 'MULE_INTERNAL', 'LATIN2');
-- WIN1250 --> MULE_INTERNAL
SELECT CONVERT('foo', 'WIN1250', 'MULE_INTERNAL');
-- MULE_INTERNAL --> WIN1250
SELECT CONVERT('foo', 'MULE_INTERNAL', 'WIN1250');
-- LATIN2 --> WIN1250
SELECT CONVERT('foo', 'LATIN2', 'WIN1250');
-- WIN1250 --> LATIN2
SELECT CONVERT('foo', 'WIN1250', 'LATIN2');
-- LATIN1 --> MULE_INTERNAL
SELECT CONVERT('foo', 'LATIN1', 'MULE_INTERNAL');
-- MULE_INTERNAL --> LATIN1
SELECT CONVERT('foo', 'MULE_INTERNAL', 'LATIN1');
-- LATIN3 --> MULE_INTERNAL
SELECT CONVERT('foo', 'LATIN3', 'MULE_INTERNAL');
-- MULE_INTERNAL --> LATIN3
SELECT CONVERT('foo', 'MULE_INTERNAL', 'LATIN3');
-- LATIN4 --> MULE_INTERNAL
SELECT CONVERT('foo', 'LATIN4', 'MULE_INTERNAL');
-- MULE_INTERNAL --> LATIN4
SELECT CONVERT('foo', 'MULE_INTERNAL', 'LATIN4');
130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
-- SQL_ASCII --> UTF8
SELECT CONVERT('foo', 'SQL_ASCII', 'UTF8');
-- UTF8 --> SQL_ASCII
SELECT CONVERT('foo', 'UTF8', 'SQL_ASCII');
-- BIG5 --> UTF8
SELECT CONVERT('foo', 'BIG5', 'UTF8');
-- UTF8 --> BIG5
SELECT CONVERT('foo', 'UTF8', 'BIG5');
-- UTF8 --> KOI8R
SELECT CONVERT('foo', 'UTF8', 'KOI8R');
-- KOI8R --> UTF8
SELECT CONVERT('foo', 'KOI8R', 'UTF8');
-- UTF8 --> WIN1251
SELECT CONVERT('foo', 'UTF8', 'WIN1251');
-- WIN1251 --> UTF8
SELECT CONVERT('foo', 'WIN1251', 'UTF8');
146 147 148 149
-- UTF8 --> WIN1252
SELECT CONVERT('foo', 'UTF8', 'WIN1252');
-- WIN1252 --> UTF8
SELECT CONVERT('foo', 'WIN1252', 'UTF8');
150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261
-- UTF8 --> WIN866
SELECT CONVERT('foo', 'UTF8', 'WIN866');
-- WIN866 --> UTF8
SELECT CONVERT('foo', 'WIN866', 'UTF8');
-- EUC_CN --> UTF8
SELECT CONVERT('foo', 'EUC_CN', 'UTF8');
-- UTF8 --> EUC_CN
SELECT CONVERT('foo', 'UTF8', 'EUC_CN');
-- EUC_JP --> UTF8
SELECT CONVERT('foo', 'EUC_JP', 'UTF8');
-- UTF8 --> EUC_JP
SELECT CONVERT('foo', 'UTF8', 'EUC_JP');
-- EUC_KR --> UTF8
SELECT CONVERT('foo', 'EUC_KR', 'UTF8');
-- UTF8 --> EUC_KR
SELECT CONVERT('foo', 'UTF8', 'EUC_KR');
-- EUC_TW --> UTF8
SELECT CONVERT('foo', 'EUC_TW', 'UTF8');
-- UTF8 --> EUC_TW
SELECT CONVERT('foo', 'UTF8', 'EUC_TW');
-- GB18030 --> UTF8
SELECT CONVERT('foo', 'GB18030', 'UTF8');
-- UTF8 --> GB18030
SELECT CONVERT('foo', 'UTF8', 'GB18030');
-- GBK --> UTF8
SELECT CONVERT('foo', 'GBK', 'UTF8');
-- UTF8 --> GBK
SELECT CONVERT('foo', 'UTF8', 'GBK');
-- UTF8 --> LATIN2
SELECT CONVERT('foo', 'UTF8', 'LATIN2');
-- LATIN2 --> UTF8
SELECT CONVERT('foo', 'LATIN2', 'UTF8');
-- UTF8 --> LATIN3
SELECT CONVERT('foo', 'UTF8', 'LATIN3');
-- LATIN3 --> UTF8
SELECT CONVERT('foo', 'LATIN3', 'UTF8');
-- UTF8 --> LATIN4
SELECT CONVERT('foo', 'UTF8', 'LATIN4');
-- LATIN4 --> UTF8
SELECT CONVERT('foo', 'LATIN4', 'UTF8');
-- UTF8 --> LATIN5
SELECT CONVERT('foo', 'UTF8', 'LATIN5');
-- LATIN5 --> UTF8
SELECT CONVERT('foo', 'LATIN5', 'UTF8');
-- UTF8 --> LATIN6
SELECT CONVERT('foo', 'UTF8', 'LATIN6');
-- LATIN6 --> UTF8
SELECT CONVERT('foo', 'LATIN6', 'UTF8');
-- UTF8 --> LATIN7
SELECT CONVERT('foo', 'UTF8', 'LATIN7');
-- LATIN7 --> UTF8
SELECT CONVERT('foo', 'LATIN7', 'UTF8');
-- UTF8 --> LATIN8
SELECT CONVERT('foo', 'UTF8', 'LATIN8');
-- LATIN8 --> UTF8
SELECT CONVERT('foo', 'LATIN8', 'UTF8');
-- UTF8 --> LATIN9
SELECT CONVERT('foo', 'UTF8', 'LATIN9');
-- LATIN9 --> UTF8
SELECT CONVERT('foo', 'LATIN9', 'UTF8');
-- UTF8 --> LATIN10
SELECT CONVERT('foo', 'UTF8', 'LATIN10');
-- LATIN10 --> UTF8
SELECT CONVERT('foo', 'LATIN10', 'UTF8');
-- UTF8 --> ISO-8859-5
SELECT CONVERT('foo', 'UTF8', 'ISO-8859-5');
-- ISO-8859-5 --> UTF8
SELECT CONVERT('foo', 'ISO-8859-5', 'UTF8');
-- UTF8 --> ISO-8859-6
SELECT CONVERT('foo', 'UTF8', 'ISO-8859-6');
-- ISO-8859-6 --> UTF8
SELECT CONVERT('foo', 'ISO-8859-6', 'UTF8');
-- UTF8 --> ISO-8859-7
SELECT CONVERT('foo', 'UTF8', 'ISO-8859-7');
-- ISO-8859-7 --> UTF8
SELECT CONVERT('foo', 'ISO-8859-7', 'UTF8');
-- UTF8 --> ISO-8859-8
SELECT CONVERT('foo', 'UTF8', 'ISO-8859-8');
-- ISO-8859-8 --> UTF8
SELECT CONVERT('foo', 'ISO-8859-8', 'UTF8');
-- LATIN1 --> UTF8
SELECT CONVERT('foo', 'LATIN1', 'UTF8');
-- UTF8 --> LATIN1
SELECT CONVERT('foo', 'UTF8', 'LATIN1');
-- JOHAB --> UTF8
SELECT CONVERT('foo', 'JOHAB', 'UTF8');
-- UTF8 --> JOHAB
SELECT CONVERT('foo', 'UTF8', 'JOHAB');
-- SJIS --> UTF8
SELECT CONVERT('foo', 'SJIS', 'UTF8');
-- UTF8 --> SJIS
SELECT CONVERT('foo', 'UTF8', 'SJIS');
-- WIN1258 --> UTF8
SELECT CONVERT('foo', 'WIN1258', 'UTF8');
-- UTF8 --> WIN1258
SELECT CONVERT('foo', 'UTF8', 'WIN1258');
-- UHC --> UTF8
SELECT CONVERT('foo', 'UHC', 'UTF8');
-- UTF8 --> UHC
SELECT CONVERT('foo', 'UTF8', 'UHC');
-- UTF8 --> WIN1250
SELECT CONVERT('foo', 'UTF8', 'WIN1250');
-- WIN1250 --> UTF8
SELECT CONVERT('foo', 'WIN1250', 'UTF8');
-- UTF8 --> WIN1256
SELECT CONVERT('foo', 'UTF8', 'WIN1256');
-- WIN1256 --> UTF8
SELECT CONVERT('foo', 'WIN1256', 'UTF8');
-- UTF8 --> WIN874
SELECT CONVERT('foo', 'UTF8', 'WIN874');
-- WIN874 --> UTF8
SELECT CONVERT('foo', 'WIN874', 'UTF8');
262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277
-- UTF8 --> WIN1253
SELECT CONVERT('foo', 'UTF8', 'WIN1253');
-- WIN1253 --> UTF8
SELECT CONVERT('foo', 'WIN1253', 'UTF8');
-- UTF8 --> WIN1254
SELECT CONVERT('foo', 'UTF8', 'WIN1254');
-- WIN1254 --> UTF8
SELECT CONVERT('foo', 'WIN1254', 'UTF8');
-- UTF8 --> WIN1255
SELECT CONVERT('foo', 'UTF8', 'WIN1255');
-- WIN1255 --> UTF8
SELECT CONVERT('foo', 'WIN1255', 'UTF8');
-- UTF8 --> WIN1257
SELECT CONVERT('foo', 'UTF8', 'WIN1257');
-- WIN1257 --> UTF8
SELECT CONVERT('foo', 'WIN1257', 'UTF8');
278 279 280 281 282 283 284 285 286 287 288 289
-- UTF8 --> EUC_JIS_2004
SELECT CONVERT('foo', 'UTF8', 'EUC_JIS_2004');
-- EUC_JIS_2004 --> UTF8
SELECT CONVERT('foo', 'EUC_JIS_2004', 'UTF8');
-- UTF8 --> SHIFT_JIS_2004
SELECT CONVERT('foo', 'UTF8', 'SHIFT_JIS_2004');
-- SHIFT_JIS_2004 --> UTF8
SELECT CONVERT('foo', 'SHIFT_JIS_2004', 'UTF8');
-- EUC_JIS_2004 --> SHIFT_JIS_2004
SELECT CONVERT('foo', 'EUC_JIS_2004', 'SHIFT_JIS_2004');
-- SHIFT_JIS_2004 --> EUC_JIS_2004
SELECT CONVERT('foo', 'SHIFT_JIS_2004', 'EUC_JIS_2004');
Tatsuo Ishii's avatar
Tatsuo Ishii committed
290 291 292
--
-- return to the super user
--
Tatsuo Ishii's avatar
Tatsuo Ishii committed
293
RESET SESSION AUTHORIZATION;
294
DROP USER conversion_test_user;