/[pg_recode]/plpgsql_recode.sql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /plpgsql_recode.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.2 - (show annotations)
Tue Sep 10 13:18:29 2002 UTC (21 years, 6 months ago) by dpavlin
Branch: MAIN
CVS Tags: HEAD
Changes since 1.1: +8 -4 lines
add "with (iscachable)" to enable function index creation

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 -- 2002-09-10 you will have to remove "with (iscachable)" if your
5 -- PostgreSQL is not current enough (>7.2.x I think). But, if you leave
6 -- it you will be able to create function index using this functions
7 -- e.g. create index ind_osobe_ime on osobe ( toczs(ime) )
8
9 -- drop old implementation
10 drop function to1250(text);
11 drop function toczs(text);
12 drop function to88592(text);
13 drop function initcap2(text);
14
15 -- convert 1250 to 8859-2
16 create function to88592(text) returns text as '
17 declare
18 c TEXT = '''';
19 new TEXT = '''';
20 pos INTEGER = 1;
21 begin
22 loop
23 c := substr($1,pos,1);
24 if c = ''š'' then new := new || ''¹'';
25 elsif c = ''Š'' then new := new || ''©'';
26 elsif c = ''ž'' then new := new || ''¾'';
27 elsif c = ''Ž'' then new := new || ''®'';
28 else new := new || c;
29 end if;
30
31 exit when pos = length($1);
32 pos := pos + 1;
33 end loop;
34 return new;
35 end
36 ' language 'PLpgSQL' with (iscachable) ;
37
38 -- convert 8859-2 to 1250
39 create function to1250(text) returns text as '
40 declare
41 c TEXT = '''';
42 new TEXT = '''';
43 pos INTEGER = 1;
44 begin
45 loop
46 c := substr($1,pos,1);
47 if c = ''¹'' then new := new || ''š'';
48 elsif c = ''©'' then new := new || ''Š'';
49 elsif c = ''¾'' then new := new || ''ž'';
50 elsif c = ''®'' then new := new || ''Ž'';
51 else new := new || c;
52 end if;
53
54 exit when pos = length($1);
55 pos := pos + 1;
56 end loop;
57 return new;
58 end
59 ' language 'PLpgSQL' with (iscachable) ;
60
61 -- tocsz -- convert iso8859-2 or cp1250 to czs
62 create function toczs(text) returns text as '
63 declare
64 c TEXT = '''';
65 new TEXT = '''';
66 pos INTEGER = 1;
67 begin
68 loop
69 c := substr($1,pos,1);
70 -- 8859-2
71 if c = ''¹'' then new := new || ''s'';
72 elsif c = ''©'' then new := new || ''S'';
73 elsif c = ''ð'' then new := new || ''dj'';
74 elsif c = ''Ð'' then new := new || ''Dj'';
75 elsif c = ''è'' then new := new || ''c'';
76 elsif c = ''È'' then new := new || ''C'';
77 elsif c = ''æ'' then new := new || ''c'';
78 elsif c = ''Æ'' then new := new || ''C'';
79 elsif c = ''¾'' then new := new || ''z'';
80 elsif c = ''®'' then new := new || ''Z'';
81 -- 1250
82 elsif c = ''š'' then new := new || ''s'';
83 elsif c = ''Š'' then new := new || ''S'';
84 elsif c = ''ž'' then new := new || ''z'';
85 elsif c = ''Ž'' then new := new || ''Z'';
86 else new := new || c;
87 end if;
88
89 exit when pos = length($1);
90 pos := pos + 1;
91 end loop;
92
93 return new;
94 end
95 ' language 'PLpgSQL' with (iscachable) ;
96
97 -- initcap2 -- initcap whith support for iso8859-2 or cp1250
98 create function initcap2(text) returns text as '
99 declare
100 c TEXT = '''';
101 new TEXT = '''';
102 pos INTEGER = 1;
103 first INTEGER = 1;
104 begin
105 loop
106 c := substr($1,pos,1);
107
108 -- new := new || ''['' || pos || '':'' || first || '']'';
109
110 -- 8859-2
111 if first = 1 then
112 if c = ''¹'' then new := new || ''©'';
113 elsif c = ''ð'' then new := new || ''Ð'';
114 elsif c = ''è'' then new := new || ''È'';
115 elsif c = ''æ'' then new := new || ''Æ'';
116 elsif c = ''¾'' then new := new || ''®'';
117 -- 1250
118 elsif c = ''š'' then new := new || ''©'';
119 elsif c = ''Š'' then new := new || ''©'';
120 elsif c = ''ž'' then new := new || ''®'';
121 elsif c = ''Ž'' then new := new || ''®'';
122 else new := new || upper(c);
123 end if;
124 else
125 if c = ''©'' then new := new || ''¹'';
126 elsif c = ''Ð'' then new := new || ''ð'';
127 elsif c = ''È'' then new := new || ''è'';
128 elsif c = ''Æ'' then new := new || ''æ'';
129 elsif c = ''®'' then new := new || ''¾'';
130 -- 1250
131 elsif c = ''š'' then new := new || ''¹'';
132 elsif c = ''Š'' then new := new || ''¹'';
133 elsif c = ''ž'' then new := new || ''¾'';
134 elsif c = ''Ž'' then new := new || ''¾'';
135 else new := new || lower(c);
136 end if;
137 end if;
138
139 exit when pos = length($1);
140 pos := pos + 1;
141
142 if c = '' '' or c = ''.'' or c = ''-'' then
143 first = 1;
144 else
145 first = 0;
146 end if;
147
148 end loop;
149
150 return new;
151 end
152 ' language 'PLpgSQL' with (iscachable) ;
153

  ViewVC Help
Powered by ViewVC 1.1.26