viewvc.cgi/pg_recode/plpgsql_recode.sql

-- this is hopefully complete implementation of pg_recode in PL/pgSQL
-- it's best for portability and I still have to check it's performance
-- 2002-08-30 Dobrica Pavlinusic <dpavlin@rot13.org>

-- drop old implementation
drop function to1250(text);
drop function toczs(text);
drop function to88592(text);
drop function initcap2(text);

-- convert 1250 to 8859-2
create function to88592(text) returns text  as '
declare
        c TEXT = '''';
        new TEXT = '''';
        pos INTEGER = 1;
begin
        loop
                c := substr($1,pos,1);
                if c = '''' then new := new || ''¹'';
                elsif c = '''' then new := new || ''©'';
                elsif c = '''' then new := new || ''¾'';
                elsif c = '''' then new := new || ''®'';
                else new := new || c;
                end if;

                exit when pos = length($1);
                pos := pos + 1;
        end loop;
        return new;
end
' language 'PLpgSQL';

-- convert 8859-2 to 1250
create function to1250(text) returns text  as '
declare
        c TEXT = '''';
        new TEXT = '''';
        pos INTEGER = 1;
begin
        loop
                c := substr($1,pos,1);
                if c = ''¹'' then new := new || '''';
                elsif c = ''©'' then new := new || '''';
                elsif c = ''¾'' then new := new || '''';
                elsif c = ''®'' then new := new || '''';
                else new := new || c;
                end if;

                exit when pos = length($1);
                pos := pos + 1;
        end loop;
        return new;
end
' language 'PLpgSQL';

-- tocsz -- convert iso8859-2 or cp1250 to czs
create function toczs(text) returns text  as '
declare
        c TEXT = '''';
        new TEXT = '''';
        pos INTEGER = 1;
begin
        loop
                c := substr($1,pos,1);
                -- 8859-2
                if c = ''¹'' then new := new || ''s'';
                elsif c = ''©'' then new := new || ''S'';
                elsif c = ''ð'' then new := new || ''dj'';
                elsif c = ''Ð'' then new := new || ''Dj'';
                elsif c = ''è'' then new := new || ''c'';
                elsif c = ''È'' then new := new || ''C'';
                elsif c = ''æ'' then new := new || ''c'';
                elsif c = ''Æ'' then new := new || ''C'';
                elsif c = ''¾'' then new := new || ''z'';
                elsif c = ''®'' then new := new || ''Z'';
                -- 1250
                elsif c = '''' then new := new || ''s'';
                elsif c = '''' then new := new || ''S'';
                elsif c = '''' then new := new || ''z'';
                elsif c = '''' then new := new || ''Z'';
                else new := new || c;
                end if;

                exit when pos = length($1);
                pos := pos + 1;
        end loop;

        return new;
end
' language 'PLpgSQL';

-- initcap2 -- initcap whith support for iso8859-2 or cp1250
create function initcap2(text) returns text  as '
declare
        c TEXT = '''';
        new TEXT = '''';
        pos INTEGER = 1;
        first INTEGER = 1;
begin
        loop
                c := substr($1,pos,1);

--              new := new || ''['' || pos || '':'' || first || '']'';

                -- 8859-2
                if first = 1 then
                        if c = ''¹'' then new := new || ''©'';
                        elsif c = ''ð'' then new := new || ''Ð'';
                        elsif c = ''è'' then new := new || ''È'';
                        elsif c = ''æ'' then new := new || ''Æ'';
                        elsif c = ''¾'' then new := new || ''®'';
                        -- 1250
                        elsif c = '''' then new := new || ''©'';
                        elsif c = '''' then new := new || ''©'';
                        elsif c = '''' then new := new || ''®'';
                        elsif c = '''' then new := new || ''®'';
                        else new := new || upper(c);
                        end if;
                else
                        if c = ''©'' then new := new || ''¹'';
                        elsif c = ''Ð'' then new := new || ''ð'';
                        elsif c = ''È'' then new := new || ''è'';
                        elsif c = ''Æ'' then new := new || ''æ'';
                        elsif c = ''®'' then new := new || ''¾'';
                        -- 1250
                        elsif c = '''' then new := new || ''¹'';
                        elsif c = '''' then new := new || ''¹'';
                        elsif c = '''' then new := new || ''¾'';
                        elsif c = '''' then new := new || ''¾'';
                        else new := new || lower(c);
                        end if;
                end if;

                exit when pos = length($1);
                pos := pos + 1;

                if c = '' '' or c = ''.'' or c = ''-'' then
                        first = 1;
                else
                        first = 0;
                end if;
                
        end loop;

        return new;
end
' language 'PLpgSQL';

1	-- this is hopefully complete implementation of pg_recode in PL/pgSQL
2	-- it's best for portability and I still have to check it's performance
3	-- 2002-08-30 Dobrica Pavlinusic <dpavlin@rot13.org>
4
5	-- drop old implementation
6	drop function to1250(text);
7	drop function toczs(text);
8	drop function to88592(text);
9	drop function initcap2(text);
10
11	-- convert 1250 to 8859-2
12	create function to88592(text) returns text as '
13	declare
14	c TEXT = '''';
15	new TEXT = '''';
16	pos INTEGER = 1;
17	begin
18	loop
19	c := substr($1,pos,1);
20	if c = '''' then new := new \|\| ''¹'';
21	elsif c = '''' then new := new \|\| ''©'';
22	elsif c = '''' then new := new \|\| ''¾'';
23	elsif c = '''' then new := new \|\| ''®'';
24	else new := new \|\| c;
25	end if;
26
27	exit when pos = length($1);
28	pos := pos + 1;
29	end loop;
30	return new;
31	end
32	' language 'PLpgSQL';
33
34	-- convert 8859-2 to 1250
35	create function to1250(text) returns text as '
36	declare
37	c TEXT = '''';
38	new TEXT = '''';
39	pos INTEGER = 1;
40	begin
41	loop
42	c := substr($1,pos,1);
43	if c = ''¹'' then new := new \|\| '''';
44	elsif c = ''©'' then new := new \|\| '''';
45	elsif c = ''¾'' then new := new \|\| '''';
46	elsif c = ''®'' then new := new \|\| '''';
47	else new := new \|\| c;
48	end if;
49
50	exit when pos = length($1);
51	pos := pos + 1;
52	end loop;
53	return new;
54	end
55	' language 'PLpgSQL';
56
57	-- tocsz -- convert iso8859-2 or cp1250 to czs
58	create function toczs(text) returns text as '
59	declare
60	c TEXT = '''';
61	new TEXT = '''';
62	pos INTEGER = 1;
63	begin
64	loop
65	c := substr($1,pos,1);
66	-- 8859-2
67	if c = ''¹'' then new := new \|\| ''s'';
68	elsif c = ''©'' then new := new \|\| ''S'';
69	elsif c = ''ð'' then new := new \|\| ''dj'';
70	elsif c = ''Ð'' then new := new \|\| ''Dj'';
71	elsif c = ''è'' then new := new \|\| ''c'';
72	elsif c = ''È'' then new := new \|\| ''C'';
73	elsif c = ''æ'' then new := new \|\| ''c'';
74	elsif c = ''Æ'' then new := new \|\| ''C'';
75	elsif c = ''¾'' then new := new \|\| ''z'';
76	elsif c = ''®'' then new := new \|\| ''Z'';
77	-- 1250
78	elsif c = '''' then new := new \|\| ''s'';
79	elsif c = '''' then new := new \|\| ''S'';
80	elsif c = '''' then new := new \|\| ''z'';
81	elsif c = '''' then new := new \|\| ''Z'';
82	else new := new \|\| c;
83	end if;
84
85	exit when pos = length($1);
86	pos := pos + 1;
87	end loop;
88
89	return new;
90	end
91	' language 'PLpgSQL';
92
93	-- initcap2 -- initcap whith support for iso8859-2 or cp1250
94	create function initcap2(text) returns text as '
95	declare
96	c TEXT = '''';
97	new TEXT = '''';
98	pos INTEGER = 1;
99	first INTEGER = 1;
100	begin
101	loop
102	c := substr($1,pos,1);
103
104	-- new := new \|\| ''['' \|\| pos \|\| '':'' \|\| first \|\| '']'';
105
106	-- 8859-2
107	if first = 1 then
108	if c = ''¹'' then new := new \|\| ''©'';
109	elsif c = ''ð'' then new := new \|\| ''Ð'';
110	elsif c = ''è'' then new := new \|\| ''È'';
111	elsif c = ''æ'' then new := new \|\| ''Æ'';
112	elsif c = ''¾'' then new := new \|\| ''®'';
113	-- 1250
114	elsif c = '''' then new := new \|\| ''©'';
115	elsif c = '''' then new := new \|\| ''©'';
116	elsif c = '''' then new := new \|\| ''®'';
117	elsif c = '''' then new := new \|\| ''®'';
118	else new := new \|\| upper(c);
119	end if;
120	else
121	if c = ''©'' then new := new \|\| ''¹'';
122	elsif c = ''Ð'' then new := new \|\| ''ð'';
123	elsif c = ''È'' then new := new \|\| ''è'';
124	elsif c = ''Æ'' then new := new \|\| ''æ'';
125	elsif c = ''®'' then new := new \|\| ''¾'';
126	-- 1250
127	elsif c = '''' then new := new \|\| ''¹'';
128	elsif c = '''' then new := new \|\| ''¹'';
129	elsif c = '''' then new := new \|\| ''¾'';
130	elsif c = '''' then new := new \|\| ''¾'';
131	else new := new \|\| lower(c);
132	end if;
133	end if;
134
135	exit when pos = length($1);
136	pos := pos + 1;
137
138	if c = '' '' or c = ''.'' or c = ''-'' then
139	first = 1;
140	else
141	first = 0;
142	end if;
143
144	end loop;
145
146	return new;
147	end
148	' language 'PLpgSQL';
149