/[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

Annotation of /plpgsql_recode.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.2 - (hide annotations)
Tue Sep 10 13:18:29 2002 UTC (16 years, 7 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 dpavlin 1.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 dpavlin 1.2 -- 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 dpavlin 1.1
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 dpavlin 1.2 ' language 'PLpgSQL' with (iscachable) ;
37 dpavlin 1.1
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 dpavlin 1.2 ' language 'PLpgSQL' with (iscachable) ;
60 dpavlin 1.1
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 dpavlin 1.2 ' language 'PLpgSQL' with (iscachable) ;
96 dpavlin 1.1
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 dpavlin 1.2 ' language 'PLpgSQL' with (iscachable) ;
153 dpavlin 1.1

  ViewVC Help
Powered by ViewVC 1.1.26