2
-- create user defined conversion
4
CREATE USER conversion_test_user WITH NOCREATEDB NOCREATEUSER;
5
SET SESSION AUTHORIZATION conversion_test_user;
6
CREATE CONVERSION myconv FOR 'LATIN1' TO 'UNICODE' FROM iso8859_1_to_utf8;
8
-- cannot make same name conversion in same schema
10
CREATE CONVERSION myconv FOR 'LATIN1' TO 'UNICODE' FROM iso8859_1_to_utf8;
11
ERROR: conversion "myconv" already exists
13
-- create default conversion with qualified name
15
CREATE DEFAULT CONVERSION public.mydef FOR 'LATIN1' TO 'UNICODE' FROM iso8859_1_to_utf8;
17
-- cannot make default conversion with same shcema/for_encoding/to_encoding
19
CREATE DEFAULT CONVERSION public.mydef2 FOR 'LATIN1' TO 'UNICODE' FROM iso8859_1_to_utf8;
20
ERROR: default conversion for LATIN1 to UNICODE already exists
22
COMMENT ON CONVERSION myconv_bad IS 'foo';
23
ERROR: conversion "myconv_bad" does not exist
24
COMMENT ON CONVERSION myconv IS 'bar';
25
COMMENT ON CONVERSION myconv IS NULL;
27
-- drop user defined conversion
29
DROP CONVERSION myconv;
30
DROP CONVERSION mydef;
32
-- make sure all pre-defined conversions are fine.
33
-- SQL_ASCII --> MULE_INTERNAL
34
SELECT CONVERT('foo' USING ascii_to_mic);
40
SELECT CONVERT('foo', 'SQL_ASCII', 'MULE_INTERNAL');
46
-- MULE_INTERNAL --> SQL_ASCII
47
SELECT CONVERT('foo' USING mic_to_ascii);
53
SELECT CONVERT('foo', 'MULE_INTERNAL', 'SQL_ASCII');
59
-- KOI8R --> MULE_INTERNAL
60
SELECT CONVERT('foo' USING koi8_r_to_mic);
66
SELECT CONVERT('foo', 'KOI8R', 'MULE_INTERNAL');
72
-- MULE_INTERNAL --> KOI8R
73
SELECT CONVERT('foo' USING mic_to_koi8_r);
79
SELECT CONVERT('foo', 'MULE_INTERNAL', 'KOI8R');
85
-- ISO-8859-5 --> MULE_INTERNAL
86
SELECT CONVERT('foo' USING iso_8859_5_to_mic);
92
SELECT CONVERT('foo', 'ISO-8859-5', 'MULE_INTERNAL');
98
-- MULE_INTERNAL --> ISO-8859-5
99
SELECT CONVERT('foo' USING mic_to_iso_8859_5);
105
SELECT CONVERT('foo', 'MULE_INTERNAL', 'ISO-8859-5');
111
-- WIN1251 --> MULE_INTERNAL
112
SELECT CONVERT('foo' USING windows_1251_to_mic);
118
SELECT CONVERT('foo', 'WIN1251', 'MULE_INTERNAL');
124
-- MULE_INTERNAL --> WIN1251
125
SELECT CONVERT('foo' USING mic_to_windows_1251);
131
SELECT CONVERT('foo', 'MULE_INTERNAL', 'WIN1251');
137
-- ALT --> MULE_INTERNAL
138
SELECT CONVERT('foo' USING windows_866_to_mic);
144
SELECT CONVERT('foo', 'ALT', 'MULE_INTERNAL');
150
-- MULE_INTERNAL --> ALT
151
SELECT CONVERT('foo' USING mic_to_windows_866);
157
SELECT CONVERT('foo', 'MULE_INTERNAL', 'ALT');
164
SELECT CONVERT('foo' USING koi8_r_to_windows_1251);
170
SELECT CONVERT('foo', 'KOI8R', 'WIN1251');
177
SELECT CONVERT('foo' USING windows_1251_to_koi8_r);
183
SELECT CONVERT('foo', 'WIN1251', 'KOI8R');
190
SELECT CONVERT('foo' USING koi8_r_to_windows_866);
196
SELECT CONVERT('foo', 'KOI8R', 'ALT');
203
SELECT CONVERT('foo' USING windows_866_to_koi8_r);
209
SELECT CONVERT('foo', 'ALT', 'KOI8R');
216
SELECT CONVERT('foo' USING windows_866_to_windows_1251);
222
SELECT CONVERT('foo', 'ALT', 'WIN1251');
229
SELECT CONVERT('foo' USING windows_1251_to_windows_866);
235
SELECT CONVERT('foo', 'WIN1251', 'ALT');
241
-- ISO-8859-5 --> KOI8R
242
SELECT CONVERT('foo' USING iso_8859_5_to_koi8_r);
248
SELECT CONVERT('foo', 'ISO-8859-5', 'KOI8R');
254
-- KOI8R --> ISO-8859-5
255
SELECT CONVERT('foo' USING koi8_r_to_iso_8859_5);
261
SELECT CONVERT('foo', 'KOI8R', 'ISO-8859-5');
267
-- ISO-8859-5 --> WIN1251
268
SELECT CONVERT('foo' USING iso_8859_5_to_windows_1251);
274
SELECT CONVERT('foo', 'ISO-8859-5', 'WIN1251');
280
-- WIN1251 --> ISO-8859-5
281
SELECT CONVERT('foo' USING windows_1251_to_iso_8859_5);
287
SELECT CONVERT('foo', 'WIN1251', 'ISO-8859-5');
293
-- ISO-8859-5 --> ALT
294
SELECT CONVERT('foo' USING iso_8859_5_to_windows_866);
300
SELECT CONVERT('foo', 'ISO-8859-5', 'ALT');
306
-- ALT --> ISO-8859-5
307
SELECT CONVERT('foo' USING windows_866_to_iso_8859_5);
313
SELECT CONVERT('foo', 'ALT', 'ISO-8859-5');
319
-- EUC_CN --> MULE_INTERNAL
320
SELECT CONVERT('foo' USING euc_cn_to_mic);
326
SELECT CONVERT('foo', 'EUC_CN', 'MULE_INTERNAL');
332
-- MULE_INTERNAL --> EUC_CN
333
SELECT CONVERT('foo' USING mic_to_euc_cn);
339
SELECT CONVERT('foo', 'MULE_INTERNAL', 'EUC_CN');
346
SELECT CONVERT('foo' USING euc_jp_to_sjis);
352
SELECT CONVERT('foo', 'EUC_JP', 'SJIS');
359
SELECT CONVERT('foo' USING sjis_to_euc_jp);
365
SELECT CONVERT('foo', 'SJIS', 'EUC_JP');
371
-- EUC_JP --> MULE_INTERNAL
372
SELECT CONVERT('foo' USING euc_jp_to_mic);
378
SELECT CONVERT('foo', 'EUC_JP', 'MULE_INTERNAL');
384
-- SJIS --> MULE_INTERNAL
385
SELECT CONVERT('foo' USING sjis_to_mic);
391
SELECT CONVERT('foo', 'SJIS', 'MULE_INTERNAL');
397
-- MULE_INTERNAL --> EUC_JP
398
SELECT CONVERT('foo' USING mic_to_euc_jp);
404
SELECT CONVERT('foo', 'MULE_INTERNAL', 'EUC_JP');
410
-- MULE_INTERNAL --> SJIS
411
SELECT CONVERT('foo' USING mic_to_sjis);
417
SELECT CONVERT('foo', 'MULE_INTERNAL', 'SJIS');
423
-- EUC_KR --> MULE_INTERNAL
424
SELECT CONVERT('foo' USING euc_kr_to_mic);
430
SELECT CONVERT('foo', 'EUC_KR', 'MULE_INTERNAL');
436
-- MULE_INTERNAL --> EUC_KR
437
SELECT CONVERT('foo' USING mic_to_euc_kr);
443
SELECT CONVERT('foo', 'MULE_INTERNAL', 'EUC_KR');
450
SELECT CONVERT('foo' USING euc_tw_to_big5);
456
SELECT CONVERT('foo', 'EUC_TW', 'BIG5');
463
SELECT CONVERT('foo' USING big5_to_euc_tw);
469
SELECT CONVERT('foo', 'BIG5', 'EUC_TW');
475
-- EUC_TW --> MULE_INTERNAL
476
SELECT CONVERT('foo' USING euc_tw_to_mic);
482
SELECT CONVERT('foo', 'EUC_TW', 'MULE_INTERNAL');
488
-- BIG5 --> MULE_INTERNAL
489
SELECT CONVERT('foo' USING big5_to_mic);
495
SELECT CONVERT('foo', 'BIG5', 'MULE_INTERNAL');
501
-- MULE_INTERNAL --> EUC_TW
502
SELECT CONVERT('foo' USING mic_to_euc_tw);
508
SELECT CONVERT('foo', 'MULE_INTERNAL', 'EUC_TW');
514
-- MULE_INTERNAL --> BIG5
515
SELECT CONVERT('foo' USING mic_to_big5);
521
SELECT CONVERT('foo', 'MULE_INTERNAL', 'BIG5');
527
-- LATIN2 --> MULE_INTERNAL
528
SELECT CONVERT('foo' USING iso_8859_2_to_mic);
534
SELECT CONVERT('foo', 'LATIN2', 'MULE_INTERNAL');
540
-- MULE_INTERNAL --> LATIN2
541
SELECT CONVERT('foo' USING mic_to_iso_8859_2);
547
SELECT CONVERT('foo', 'MULE_INTERNAL', 'LATIN2');
553
-- WIN1250 --> MULE_INTERNAL
554
SELECT CONVERT('foo' USING windows_1250_to_mic);
560
SELECT CONVERT('foo', 'WIN1250', 'MULE_INTERNAL');
566
-- MULE_INTERNAL --> WIN1250
567
SELECT CONVERT('foo' USING mic_to_windows_1250);
573
SELECT CONVERT('foo', 'MULE_INTERNAL', 'WIN1250');
579
-- LATIN2 --> WIN1250
580
SELECT CONVERT('foo' USING iso_8859_2_to_windows_1250);
586
SELECT CONVERT('foo', 'LATIN2', 'WIN1250');
592
-- WIN1250 --> LATIN2
593
SELECT CONVERT('foo' USING windows_1250_to_iso_8859_2);
599
SELECT CONVERT('foo', 'WIN1250', 'LATIN2');
605
-- LATIN1 --> MULE_INTERNAL
606
SELECT CONVERT('foo' USING iso_8859_1_to_mic);
612
SELECT CONVERT('foo', 'LATIN1', 'MULE_INTERNAL');
618
-- MULE_INTERNAL --> LATIN1
619
SELECT CONVERT('foo' USING mic_to_iso_8859_1);
625
SELECT CONVERT('foo', 'MULE_INTERNAL', 'LATIN1');
631
-- LATIN3 --> MULE_INTERNAL
632
SELECT CONVERT('foo' USING iso_8859_3_to_mic);
638
SELECT CONVERT('foo', 'LATIN3', 'MULE_INTERNAL');
644
-- MULE_INTERNAL --> LATIN3
645
SELECT CONVERT('foo' USING mic_to_iso_8859_3);
651
SELECT CONVERT('foo', 'MULE_INTERNAL', 'LATIN3');
657
-- LATIN4 --> MULE_INTERNAL
658
SELECT CONVERT('foo' USING iso_8859_4_to_mic);
664
SELECT CONVERT('foo', 'LATIN4', 'MULE_INTERNAL');
670
-- MULE_INTERNAL --> LATIN4
671
SELECT CONVERT('foo' USING mic_to_iso_8859_4);
677
SELECT CONVERT('foo', 'MULE_INTERNAL', 'LATIN4');
683
-- SQL_ASCII --> UNICODE
684
SELECT CONVERT('foo' USING ascii_to_utf_8);
690
SELECT CONVERT('foo', 'SQL_ASCII', 'UNICODE');
696
-- UNICODE --> SQL_ASCII
697
SELECT CONVERT('foo' USING utf_8_to_ascii);
703
SELECT CONVERT('foo', 'UNICODE', 'SQL_ASCII');
710
SELECT CONVERT('foo' USING big5_to_utf_8);
716
SELECT CONVERT('foo', 'BIG5', 'UNICODE');
723
SELECT CONVERT('foo' USING utf_8_to_big5);
729
SELECT CONVERT('foo', 'UNICODE', 'BIG5');
736
SELECT CONVERT('foo' USING utf_8_to_koi8_r);
742
SELECT CONVERT('foo', 'UNICODE', 'KOI8R');
749
SELECT CONVERT('foo' USING koi8_r_to_utf_8);
755
SELECT CONVERT('foo', 'KOI8R', 'UNICODE');
761
-- UNICODE --> WIN1251
762
SELECT CONVERT('foo' USING utf_8_to_windows_1251);
768
SELECT CONVERT('foo', 'UNICODE', 'WIN1251');
774
-- WIN1251 --> UNICODE
775
SELECT CONVERT('foo' USING windows_1251_to_utf_8);
781
SELECT CONVERT('foo', 'WIN1251', 'UNICODE');
788
SELECT CONVERT('foo' USING utf_8_to_windows_866);
794
SELECT CONVERT('foo', 'UNICODE', 'ALT');
801
SELECT CONVERT('foo' USING windows_866_to_utf_8);
807
SELECT CONVERT('foo', 'ALT', 'UNICODE');
813
-- EUC_CN --> UNICODE
814
SELECT CONVERT('foo' USING euc_cn_to_utf_8);
820
SELECT CONVERT('foo', 'EUC_CN', 'UNICODE');
826
-- UNICODE --> EUC_CN
827
SELECT CONVERT('foo' USING utf_8_to_euc_cn);
833
SELECT CONVERT('foo', 'UNICODE', 'EUC_CN');
839
-- EUC_JP --> UNICODE
840
SELECT CONVERT('foo' USING euc_jp_to_utf_8);
846
SELECT CONVERT('foo', 'EUC_JP', 'UNICODE');
852
-- UNICODE --> EUC_JP
853
SELECT CONVERT('foo' USING utf_8_to_euc_jp);
859
SELECT CONVERT('foo', 'UNICODE', 'EUC_JP');
865
-- EUC_KR --> UNICODE
866
SELECT CONVERT('foo' USING euc_kr_to_utf_8);
872
SELECT CONVERT('foo', 'EUC_KR', 'UNICODE');
878
-- UNICODE --> EUC_KR
879
SELECT CONVERT('foo' USING utf_8_to_euc_kr);
885
SELECT CONVERT('foo', 'UNICODE', 'EUC_KR');
891
-- EUC_TW --> UNICODE
892
SELECT CONVERT('foo' USING euc_tw_to_utf_8);
898
SELECT CONVERT('foo', 'EUC_TW', 'UNICODE');
904
-- UNICODE --> EUC_TW
905
SELECT CONVERT('foo' USING utf_8_to_euc_tw);
911
SELECT CONVERT('foo', 'UNICODE', 'EUC_TW');
917
-- GB18030 --> UNICODE
918
SELECT CONVERT('foo' USING gb18030_to_utf_8);
924
SELECT CONVERT('foo', 'GB18030', 'UNICODE');
930
-- UNICODE --> GB18030
931
SELECT CONVERT('foo' USING utf_8_to_gb18030);
937
SELECT CONVERT('foo', 'UNICODE', 'GB18030');
944
SELECT CONVERT('foo' USING gbk_to_utf_8);
950
SELECT CONVERT('foo', 'GBK', 'UNICODE');
957
SELECT CONVERT('foo' USING utf_8_to_gbk);
963
SELECT CONVERT('foo', 'UNICODE', 'GBK');
969
-- UNICODE --> LATIN2
970
SELECT CONVERT('foo' USING utf_8_to_iso_8859_2);
976
SELECT CONVERT('foo', 'UNICODE', 'LATIN2');
982
-- LATIN2 --> UNICODE
983
SELECT CONVERT('foo' USING iso_8859_2_to_utf_8);
989
SELECT CONVERT('foo', 'LATIN2', 'UNICODE');
995
-- UNICODE --> LATIN3
996
SELECT CONVERT('foo' USING utf_8_to_iso_8859_3);
1002
SELECT CONVERT('foo', 'UNICODE', 'LATIN3');
1008
-- LATIN3 --> UNICODE
1009
SELECT CONVERT('foo' USING iso_8859_3_to_utf_8);
1015
SELECT CONVERT('foo', 'LATIN3', 'UNICODE');
1021
-- UNICODE --> LATIN4
1022
SELECT CONVERT('foo' USING utf_8_to_iso_8859_4);
1028
SELECT CONVERT('foo', 'UNICODE', 'LATIN4');
1034
-- LATIN4 --> UNICODE
1035
SELECT CONVERT('foo' USING iso_8859_4_to_utf_8);
1041
SELECT CONVERT('foo', 'LATIN4', 'UNICODE');
1047
-- UNICODE --> LATIN5
1048
SELECT CONVERT('foo' USING utf_8_to_iso_8859_9);
1054
SELECT CONVERT('foo', 'UNICODE', 'LATIN5');
1060
-- LATIN5 --> UNICODE
1061
SELECT CONVERT('foo' USING iso_8859_9_to_utf_8);
1067
SELECT CONVERT('foo', 'LATIN5', 'UNICODE');
1073
-- UNICODE --> LATIN6
1074
SELECT CONVERT('foo' USING utf_8_to_iso_8859_10);
1080
SELECT CONVERT('foo', 'UNICODE', 'LATIN6');
1086
-- LATIN6 --> UNICODE
1087
SELECT CONVERT('foo' USING iso_8859_10_to_utf_8);
1093
SELECT CONVERT('foo', 'LATIN6', 'UNICODE');
1099
-- UNICODE --> LATIN7
1100
SELECT CONVERT('foo' USING utf_8_to_iso_8859_13);
1106
SELECT CONVERT('foo', 'UNICODE', 'LATIN7');
1112
-- LATIN7 --> UNICODE
1113
SELECT CONVERT('foo' USING iso_8859_13_to_utf_8);
1119
SELECT CONVERT('foo', 'LATIN7', 'UNICODE');
1125
-- UNICODE --> LATIN8
1126
SELECT CONVERT('foo' USING utf_8_to_iso_8859_14);
1132
SELECT CONVERT('foo', 'UNICODE', 'LATIN8');
1138
-- LATIN8 --> UNICODE
1139
SELECT CONVERT('foo' USING iso_8859_14_to_utf_8);
1145
SELECT CONVERT('foo', 'LATIN8', 'UNICODE');
1151
-- UNICODE --> LATIN9
1152
SELECT CONVERT('foo' USING utf_8_to_iso_8859_15);
1158
SELECT CONVERT('foo', 'UNICODE', 'LATIN9');
1164
-- LATIN9 --> UNICODE
1165
SELECT CONVERT('foo' USING iso_8859_15_to_utf_8);
1171
SELECT CONVERT('foo', 'LATIN9', 'UNICODE');
1177
-- UNICODE --> LATIN10
1178
SELECT CONVERT('foo' USING utf_8_to_iso_8859_16);
1184
SELECT CONVERT('foo', 'UNICODE', 'LATIN10');
1190
-- LATIN10 --> UNICODE
1191
SELECT CONVERT('foo' USING iso_8859_16_to_utf_8);
1197
SELECT CONVERT('foo', 'LATIN10', 'UNICODE');
1203
-- UNICODE --> ISO-8859-5
1204
SELECT CONVERT('foo' USING utf_8_to_iso_8859_5);
1210
SELECT CONVERT('foo', 'UNICODE', 'ISO-8859-5');
1216
-- ISO-8859-5 --> UNICODE
1217
SELECT CONVERT('foo' USING iso_8859_5_to_utf_8);
1223
SELECT CONVERT('foo', 'ISO-8859-5', 'UNICODE');
1229
-- UNICODE --> ISO-8859-6
1230
SELECT CONVERT('foo' USING utf_8_to_iso_8859_6);
1236
SELECT CONVERT('foo', 'UNICODE', 'ISO-8859-6');
1242
-- ISO-8859-6 --> UNICODE
1243
SELECT CONVERT('foo' USING iso_8859_6_to_utf_8);
1249
SELECT CONVERT('foo', 'ISO-8859-6', 'UNICODE');
1255
-- UNICODE --> ISO-8859-7
1256
SELECT CONVERT('foo' USING utf_8_to_iso_8859_7);
1262
SELECT CONVERT('foo', 'UNICODE', 'ISO-8859-7');
1268
-- ISO-8859-7 --> UNICODE
1269
SELECT CONVERT('foo' USING iso_8859_7_to_utf_8);
1275
SELECT CONVERT('foo', 'ISO-8859-7', 'UNICODE');
1281
-- UNICODE --> ISO-8859-8
1282
SELECT CONVERT('foo' USING utf_8_to_iso_8859_8);
1288
SELECT CONVERT('foo', 'UNICODE', 'ISO-8859-8');
1294
-- ISO-8859-8 --> UNICODE
1295
SELECT CONVERT('foo' USING iso_8859_8_to_utf_8);
1301
SELECT CONVERT('foo', 'ISO-8859-8', 'UNICODE');
1307
-- LATIN1 --> UNICODE
1308
SELECT CONVERT('foo' USING iso_8859_1_to_utf_8);
1314
SELECT CONVERT('foo', 'LATIN1', 'UNICODE');
1320
-- UNICODE --> LATIN1
1321
SELECT CONVERT('foo' USING utf_8_to_iso_8859_1);
1327
SELECT CONVERT('foo', 'UNICODE', 'LATIN1');
1333
-- JOHAB --> UNICODE
1334
SELECT CONVERT('foo' USING johab_to_utf_8);
1340
SELECT CONVERT('foo', 'JOHAB', 'UNICODE');
1346
-- UNICODE --> JOHAB
1347
SELECT CONVERT('foo' USING utf_8_to_johab);
1353
SELECT CONVERT('foo', 'UNICODE', 'JOHAB');
1360
SELECT CONVERT('foo' USING sjis_to_utf_8);
1366
SELECT CONVERT('foo', 'SJIS', 'UNICODE');
1373
SELECT CONVERT('foo' USING utf_8_to_sjis);
1379
SELECT CONVERT('foo', 'UNICODE', 'SJIS');
1386
SELECT CONVERT('foo' USING tcvn_to_utf_8);
1392
SELECT CONVERT('foo', 'TCVN', 'UNICODE');
1399
SELECT CONVERT('foo' USING utf_8_to_tcvn);
1405
SELECT CONVERT('foo', 'UNICODE', 'TCVN');
1412
SELECT CONVERT('foo' USING uhc_to_utf_8);
1418
SELECT CONVERT('foo', 'UHC', 'UNICODE');
1425
SELECT CONVERT('foo' USING utf_8_to_uhc);
1431
SELECT CONVERT('foo', 'UNICODE', 'UHC');
1437
-- UNICODE --> WIN1250
1438
SELECT CONVERT('foo' USING utf_8_to_windows_1250);
1444
SELECT CONVERT('foo', 'UNICODE', 'WIN1250');
1450
-- WIN1250 --> UNICODE
1451
SELECT CONVERT('foo' USING windows_1250_to_utf_8);
1457
SELECT CONVERT('foo', 'WIN1250', 'UNICODE');
1463
-- UNICODE --> WIN1256
1464
SELECT CONVERT('foo' USING utf_8_to_windows_1256);
1470
SELECT CONVERT('foo', 'UNICODE', 'WIN1256');
1476
-- WIN1256 --> UNICODE
1477
SELECT CONVERT('foo' USING windows_1256_to_utf_8);
1483
SELECT CONVERT('foo', 'WIN1256', 'UNICODE');
1489
-- UNICODE --> WIN874
1490
SELECT CONVERT('foo' USING utf_8_to_windows_874);
1496
SELECT CONVERT('foo', 'UNICODE', 'WIN874');
1502
-- WIN874 --> UNICODE
1503
SELECT CONVERT('foo' USING windows_874_to_utf_8);
1509
SELECT CONVERT('foo', 'WIN874', 'UNICODE');
1516
-- return to the super user
1518
RESET SESSION AUTHORIZATION;
1519
DROP USER conversion_test_user;