2 -- Glyn Astill 09/02/2014
3 -- Plperl untrusted functions to do conversion from DOS cp850 native through Latin1 to UTF8 and vice versa
6 -- The following functions require the user to know what their client_encoiding is and call the appropriate function
8 DROP FUNCTION IF EXISTS cp850_to_latin1(text);
9 CREATE OR REPLACE FUNCTION cp850_to_latin1(text)
13 return encode( 'utf8', decode('cp850', $_[0] ));
15 LANGUAGE 'plperlu' IMMUTABLE;
17 COMMENT ON FUNCTION public.cp850_to_latin1(text) IS 'Converts text from cp850 to current latin1';
21 DROP FUNCTION IF EXISTS cp850_to_utf8(text);
22 CREATE OR REPLACE FUNCTION cp850_to_utf8(text)
26 return encode( 'iso-8859-1', decode('cp850', $_[0] ));
28 LANGUAGE 'plperlu' IMMUTABLE;
30 COMMENT ON FUNCTION public.cp850_to_utf8(text) IS 'Converts text from cp850 to current utf8';
34 DROP FUNCTION IF EXISTS latin1_to_cp850(text);
35 CREATE OR REPLACE FUNCTION latin1_to_cp850(text)
39 return encode( 'cp850', decode('utf8', $_[0] ));
41 LANGUAGE 'plperlu' IMMUTABLE;
43 COMMENT ON FUNCTION public.latin1_to_cp850(text) IS 'Converts text from current latin1 to cp850';
47 DROP FUNCTION IF EXISTS utf8_to_cp850(text);
48 CREATE OR REPLACE FUNCTION utf8_to_cp850(text)
52 return encode( 'cp850', decode('iso-8859-1', $_[0] ));
54 LANGUAGE 'plperlu' IMMUTABLE;
56 COMMENT ON FUNCTION public.utf8_to_cp850(text) IS 'Converts text from current utf8 to cp850';
58 -- The following functions do not require the user to know what their client_encoiding is
60 DROP FUNCTION IF EXISTS public.to_cp850(text);
61 CREATE OR REPLACE FUNCTION public.to_cp850(text)
66 my $rv = spi_exec_query('SELECT pg_client_encoding();', 1);
67 my $encoding = $rv->{rows}[0]->{pg_client_encoding};
70 if ($encoding eq 'UTF8') {
71 $string = encode( 'cp850', decode('iso-8859-1', $_[0] ));
73 elsif (($encoding eq 'LATIN1') || ($encoding eq 'SQL_ASCII')){
74 $string = encode( 'cp850', decode('utf8', $_[0] ));
77 elog(ERROR, "to_cp850 currently does not support client_encoding $encoding");
81 elog(WARNING, "Re-encoding failed, stripping non alphanumerics: $@");
82 $string = $_[0] =~ s/[\x01-\x7f]/?/g;
86 LANGUAGE 'plperlu' IMMUTABLE;
88 COMMENT ON FUNCTION public.to_cp850(text) IS 'Converts text from current client_encoding to cp850';
92 DROP FUNCTION IF EXISTS public.from_cp850(text);
93 CREATE OR REPLACE FUNCTION public.from_cp850(text)
98 my $rv = spi_exec_query('SELECT pg_client_encoding();', 1);
99 my $encoding = $rv->{rows}[0]->{pg_client_encoding};
102 if ($encoding eq 'UTF8') {
103 $string = encode( 'iso-8859-1', decode('cp850', $_[0] ));
105 elsif (($encoding eq 'LATIN1') || ($encoding eq 'SQL_ASCII')){
106 $string = encode( 'utf8', decode('cp850', $_[0] ));
109 elog(ERROR, "from_cp850 currently does not support client_encoding $encoding");
113 elog(WARNING, "Re-encoding failed: $@");
120 LANGUAGE 'plperlu' IMMUTABLE;
122 COMMENT ON FUNCTION public.from_cp850(text) IS 'Converts text from cp850 to current client_encoding';