cr_func_generic.sql
11.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
-- allgemeine Funktionen --
-- [
create function start_logging(text, text)
returns int4 as '
/*
* start_logging(table-name, operation) (INSERT, UPDATE, DELETE)
*
* Startet das logging zu einer bestimmten table.
*
* returns: 1 - logging eingeschaltet
* 0 - logging war schon eingeschaltet
*/
DECLARE
table_schema text;
table_schema_oid int4;
trig_name_before text;
trig_name_after text;
all_actions _text := ''{\"INSERT\", \"UPDATE\", \"DELETE\"}'';
actions _text;
range int4;
query text;
BEGIN
IF $2 <> ''INSERT'' AND
$2 <> ''UPDATE'' AND
$2 <> ''DELETE'' AND
$2 <> ''CHANGE'' AND
$2 <> ''ALL'' THEN
RETURN -1;
END IF;
select into table_schema_oid relnamespace
from pg_catalog.pg_class where relname=$1;
select into table_schema nspname from pg_catalog.pg_namespace where
oid=table_schema_oid;
IF $2 = ''ALL'' THEN
range := 3;
actions := all_actions;
ELSE
IF $2 = ''CHANGE'' THEN
range := 2;
actions := ''{'';
FOR i IN 1..2 LOOP
actions := actions || quote_ident(all_actions[i]);
IF i<2 THEN
actions := actions || '','';
ELSE
actions := actions || ''}'';
END IF;
END LOOP;
ELSE
range := 1;
actions := ''{'' || quote_ident($2) || ''}'';
END IF;
END IF;
FOR i IN 1..range LOOP
trig_name_before := ''log_before_'' || lower(actions[i]) ||
''_'' || $1;
trig_name_after := ''log_after_'' || lower(actions[i]) ||
''_'' || $1;
PERFORM * from pg_catalog.pg_trigger where
tgname=trig_name_before;
IF NOT FOUND THEN
query := ''CREATE TRIGGER '' || trig_name_before ||
'' BEFORE '' || actions[i] || '' ON '' ||
table_schema || ''.'' || $1 ||
'' FOR EACH ROW EXECUTE PROCEDURE '' ||
''trigger_func.set_history();'';
EXECUTE query;
query := ''CREATE TRIGGER '' || trig_name_after ||
'' AFTER '' || actions[i] || '' ON '' ||
table_schema || ''.'' || $1 ||
'' FOR EACH ROW EXECUTE PROCEDURE '' ||
''trigger_func.set_history();'';
EXECUTE query;
ELSE
RAISE EXCEPTION ''specified trigger not found'';
END IF;
END LOOP;
RETURN 1;
END;
' language 'plpgsql';
create function stop_logging(text, text)
returns int4 as '
/*
* stop_logging(table-name, operation) (INSERT, UPDATE, DELETE)
*
* Stoppt das logging zu einer bestimmten table.
*
* returns: 1 - logging eingeschaltet
* 0 - logging war schon eingeschaltet
*/
DECLARE
table_schema text;
table_schema_oid int4;
trig_name_before text;
trig_name_after text;
all_actions _text := ''{\"INSERT\", \"UPDATE\", \"DELETE\"}'';
actions _text;
range int4;
query text;
BEGIN
IF $2 <> ''INSERT'' AND
$2 <> ''UPDATE'' AND
$2 <> ''DELETE'' AND
$2 <> ''CHANGE'' AND
$2 <> ''ALL'' THEN
RETURN -1;
END IF;
select into table_schema_oid relnamespace
from pg_catalog.pg_class where relname=$1;
select into table_schema nspname from pg_catalog.pg_namespace where
oid=table_schema_oid;
IF $2 = ''ALL'' THEN
range := 3;
actions := all_actions;
ELSE
IF $2 = ''CHANGE'' THEN
range := 2;
actions := ''{'';
FOR i IN 1..2 LOOP
actions := actions || quote_ident(all_actions[i]);
IF i<2 THEN
actions := actions || '','';
ELSE
actions := actions || ''}'';
END IF;
END LOOP;
ELSE
range := 1;
actions := ''{'' || quote_ident($2) || ''}'';
END IF;
END IF;
FOR i IN 1..range LOOP
trig_name_before := ''log_before_'' || lower(actions[i]) ||
''_'' || $1;
trig_name_after := ''log_after_'' || lower(actions[i]) ||
''_'' || $1;
PERFORM * from pg_catalog.pg_trigger where
tgname=trig_name_before;
IF FOUND THEN
query := ''DROP TRIGGER '' || trig_name_before ||
'' ON '' || table_schema || ''.'' || $1 || '';'';
EXECUTE query;
query := ''DROP TRIGGER '' || trig_name_after ||
'' ON '' || table_schema || ''.'' || $1 || '';'';
EXECUTE query;
ELSE
RAISE EXCEPTION ''specified trigger not found'';
END IF;
END LOOP;
RETURN 1;
END;
' language 'plpgsql';
create function bit2text(bit varying)
returns text as '
DECLARE
len int4;
i int4;
conv int4;
ret text := '''';
BEGIN
len := bit_length($1);
FOR i IN 0..len-1 LOOP
conv := ($1<<i)::BIT(1)::int4;
ret := ret || conv;
END LOOP;
RETURN ret;
END;
' language 'plpgsql';
create function bit_set_len(bit varying, int4)
returns bit varying as '
/*
* bitlen(zu aenderneder Bitstring, len)
*
* Postres hat die dumme regelung, das wenn man die laenge eines
* Bitstrings umcastet immer rechts weggeschnitten oder ergaenzt
* wird. Erwarten tut man allerdings normalerweise das diese dinge
* links passieren, damit der Wert der Bitfolge nicht veraendert, bzw.
* die Positionen der bisherigen Bits nicht veraendert werden.
* genau das macht diese Funktion.
*
* returns: Den veraenderten Bitstring
*/
DECLARE
old_len int4;
sel text;
dummy RECORD;
BEGIN
old_len := bit_length($1);
IF old_len < $2 THEN
sel := ''select B'' || quote_literal(bit2text($1)) ||
''::BIT('' || $2 || '')>>'' || $2-old_len ||
'' as new_val'';
FOR dummy IN EXECUTE sel LOOP
END LOOP;
ELSE
IF old_len > $2 THEN
sel := ''select (B'' || quote_literal(bit2text($1)) ||
''<<'' || old_len-$2 || '')::BIT('' || $2 ||
'') as new_val'';
FOR dummy IN EXECUTE sel LOOP
END LOOP;
ELSE
RETURN $1;
END IF;
END IF;
RETURN dummy.new_val;
END;
' language 'plpgsql';
create function mask_len(text, text, text)
returns int4 as '
DECLARE
len int4 := 0;
result RECORD;
query text;
i int4 := 0;
BEGIN
query := ''select bit_length('' || $2 || '') '' ||
''as len from '' || $1;
FOR result IN EXECUTE query LOOP
IF i = 0 THEN
len := result.len;
i := 1;
END IF;
IF $3 = ''min'' THEN
IF result.len < len THEN
len := result.len;
END IF;
END IF;
IF $3 = ''max'' THEN
IF result.len > len THEN
len := result.len;
END IF;
END IF;
IF $3 IS NULL OR $3 = ''first'' THEN
EXIT;
END IF;
END LOOP;
IF NOT FOUND THEN
len := 0;
END IF;
RETURN len;
END;
' language 'plpgsql';
create function corrected_mask(text, text, bit varying)
returns bit varying as '
DECLARE
len int4;
new bit varying;
BEGIN
select INTO len public.mask_len($1, $2, NULL);
IF len < bit_length($3) THEN
RETURN $3;
ELSE
RETURN bit_set_len($3, len);
END IF;
END;
' language 'plpgsql';
create function get_db_comment(text)
returns text as '
DECLARE
co text;
dboid pg_catalog.pg_stat_database.datid%TYPE;
BEGIN
select into dboid OID from pg_catalog.pg_database where
datname=$1;
select into co description from pg_catalog.pg_description where
objoid=dboid;
return co;
END;
' language 'plpgsql';
create function get_schema_comment(text)
returns text as '
DECLARE
co text;
schemaoid pg_catalog.pg_class.relnamespace%TYPE;
BEGIN
select into schemaoid OID from pg_catalog.pg_namespace where
nspname=$1;
select into co description from pg_catalog.pg_description where
objoid=schemaoid;
return co;
END;
' language 'plpgsql';
create function get_constraint_comment(text)
returns text as '
DECLARE
co text;
conoid pg_catalog.pg_stat_database.datid%TYPE;
BEGIN
select into conoid OID from pg_catalog.pg_constraint where
conname=$1;
select into co description from pg_catalog.pg_description where
objoid=conoid;
return co;
END;
' language 'plpgsql';
-- CREATE FUNCTION jdbc_clean(text) RETURNS text AS'
-- /*
-- * Brauchte ich aufgrund eines Bugs im jdbc Treiber, der LATIN1 nicht
-- * korrekt in UNICODE umwandelte.
-- * Jetzt ist dies Funktion obsolet.
-- *
-- */
-- DECLARE
-- ret text;
-- BEGIN
-- SELECT INTO ret replace($1, ''\\\\344'', ''ä'');
-- SELECT INTO ret replace(ret, ''\\\\366'', ''ö'');
-- SELECT INTO ret replace(ret, ''\\\\374'', ''ü'');
-- SELECT INTO ret replace(ret, ''\\\\337'', ''\ß'');
-- SELECT INTO ret replace(ret, ''\\\\304'', ''Ä'');
-- SELECT INTO ret replace(ret, ''\\\\326'', ''Ö'');
-- SELECT INTO ret replace(ret, ''\\\\334'', ''Ü'');
--
-- RETURN ret;
-- END;
--' LANGUAGE 'plpgsql'
-- ]
-- allgemeine Funktionen --