1 #include "postgres.h"
2
3 #include "access/htup_details.h"
4 #include "catalog/pg_type.h"
5 #include "commands/trigger.h"
6 #include "executor/spi.h"
7 #include "miscadmin.h"
8 #include "parser/parse_coerce.h"
9 #include "utils/builtins.h"
10 #include "utils/lsyscache.h"
11 #include "utils/rel.h"
12
13 #include "orafce.h"
14 #include "builtins.h"
15
16 PG_FUNCTION_INFO_V1(orafce_replace_empty_strings);
17 PG_FUNCTION_INFO_V1(orafce_replace_null_strings);
18
19 #if PG_VERSION_NUM < 100000
20
21 static HeapTuple
heap_modify_tuple_by_cols(HeapTuple tuple,TupleDesc tupleDesc,int nCols,int * replCols,Datum * replValues,bool * replIsnull)22 heap_modify_tuple_by_cols(HeapTuple tuple,
23 TupleDesc tupleDesc,
24 int nCols,
25 int *replCols,
26 Datum *replValues,
27 bool *replIsnull)
28 {
29 int numberOfAttributes = tupleDesc->natts;
30 Datum *values;
31 bool *isnull;
32 HeapTuple newTuple;
33 int i;
34
35 /*
36 * allocate and fill values and isnull arrays from the tuple, then replace
37 * selected columns from the input arrays.
38 */
39 values = (Datum *) palloc(numberOfAttributes * sizeof(Datum));
40 isnull = (bool *) palloc(numberOfAttributes * sizeof(bool));
41
42 heap_deform_tuple(tuple, tupleDesc, values, isnull);
43
44 for (i = 0; i < nCols; i++)
45 {
46 int attnum = replCols[i];
47
48 if (attnum <= 0 || attnum > numberOfAttributes)
49 elog(ERROR, "invalid column number %d", attnum);
50 values[attnum - 1] = replValues[i];
51 isnull[attnum - 1] = replIsnull[i];
52 }
53
54 /*
55 * create a new tuple from the values and isnull arrays
56 */
57 newTuple = heap_form_tuple(tupleDesc, values, isnull);
58
59 pfree(values);
60 pfree(isnull);
61
62 /*
63 * copy the identification info of the old tuple: t_ctid, t_self, and OID
64 * (if any)
65 */
66 newTuple->t_data->t_ctid = tuple->t_data->t_ctid;
67 newTuple->t_self = tuple->t_self;
68 newTuple->t_tableOid = tuple->t_tableOid;
69 if (tupleDesc->tdhasoid)
70 HeapTupleSetOid(newTuple, HeapTupleGetOid(tuple));
71
72 return newTuple;
73 }
74
75 #endif
76
77 static void
trigger_sanity_check(FunctionCallInfo fcinfo,const char * fname)78 trigger_sanity_check(FunctionCallInfo fcinfo, const char *fname)
79 {
80 TriggerData *trigdata = (TriggerData *) fcinfo->context;
81
82 /* sanity checks from autoinc.c */
83 if (!CALLED_AS_TRIGGER(fcinfo))
84 elog(ERROR, "%s: not fired by trigger manager", fname);
85
86 if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
87 elog(ERROR, "%s: must be fired for row", fname);
88
89 if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event))
90 elog(ERROR, "%s: must be fired before event", fname);
91
92 if (trigdata->tg_trigger->tgnargs > 1)
93 elog(ERROR, "%s: only one trigger parameter is allowed", fname);
94 }
95
96 static HeapTuple
get_rettuple(FunctionCallInfo fcinfo)97 get_rettuple(FunctionCallInfo fcinfo)
98 {
99 TriggerData *trigdata = (TriggerData *) fcinfo->context;
100 HeapTuple rettuple = NULL;
101
102 if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
103 rettuple = trigdata->tg_trigtuple;
104 else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
105 rettuple = trigdata->tg_newtuple;
106 else
107 /* internal error */
108 elog(ERROR, "remove_empty_string: cannot process DELETE events");
109
110 return rettuple;
111 }
112
113 /*
114 * Trigger argument is used as parameter that can enforce warning about modified
115 * columns. When first argument is "on" or "true", then warnings will be raised.
116 */
117 static bool
should_raise_warnings(FunctionCallInfo fcinfo)118 should_raise_warnings(FunctionCallInfo fcinfo)
119 {
120 TriggerData *trigdata = (TriggerData *) fcinfo->context;
121 Trigger *trigger = trigdata->tg_trigger;
122
123 if (trigger->tgnargs > 0)
124 {
125 char **args = trigger->tgargs;
126
127 if (strcmp(args[0], "on") == 0 ||
128 strcmp(args[0], "true") == 0)
129 return true;
130 }
131
132 return false;
133 }
134
135 /*
136 * Detects emty strings in type text based fields and replaces them by NULL.
137 */
138 Datum
orafce_replace_empty_strings(PG_FUNCTION_ARGS)139 orafce_replace_empty_strings(PG_FUNCTION_ARGS)
140 {
141 TriggerData *trigdata = (TriggerData *) fcinfo->context;
142 HeapTuple rettuple = NULL;
143 TupleDesc tupdesc;
144 int *resetcols = NULL;
145 Datum *values = NULL;
146 bool *nulls = NULL;
147 Oid prev_typid = InvalidOid;
148 bool is_string = false;
149 int nresetcols = 0;
150 int attnum;
151 bool raise_warning = false;
152 char *relname = NULL;
153
154 trigger_sanity_check(fcinfo, "replace_empty_strings");
155 raise_warning = should_raise_warnings(fcinfo);
156
157 rettuple = get_rettuple(fcinfo);
158 tupdesc = trigdata->tg_relation->rd_att;
159
160 /* iterate over record's fields */
161 for (attnum = 1; attnum <= tupdesc->natts; attnum++)
162 {
163 Oid typid;
164
165 /* simple cache - lot of time columns with same type is side by side */
166 typid = SPI_gettypeid(tupdesc, attnum);
167 if (typid != prev_typid)
168 {
169 TYPCATEGORY category;
170 bool ispreferred;
171 Oid base_typid;
172
173 base_typid = getBaseType(typid);
174 get_type_category_preferred(base_typid, &category, &ispreferred);
175
176 is_string = (category == TYPCATEGORY_STRING);
177 prev_typid = typid;
178 }
179
180 if (is_string)
181 {
182 Datum value;
183 bool isnull;
184
185 value = SPI_getbinval(rettuple, tupdesc, attnum, &isnull);
186 if (!isnull)
187 {
188 text *txt = DatumGetTextP(value);
189
190 /* is it empty string (has zero length */
191 if (VARSIZE_ANY_EXHDR(txt) == 0)
192 {
193 if (!resetcols)
194 {
195 /* lazy allocation of dynamic memory */
196 resetcols = palloc0(tupdesc->natts * sizeof(int));
197 nulls = palloc0(tupdesc->natts * sizeof(bool));
198 values = palloc0(tupdesc->natts * sizeof(Datum));
199 }
200
201 resetcols[nresetcols] = attnum;
202 values[nresetcols] = (Datum) 0;
203 nulls[nresetcols++] = true;
204
205 if (raise_warning)
206 {
207 if (!relname)
208 relname = SPI_getrelname(trigdata->tg_relation);
209
210 elog(WARNING,
211 "Field \"%s\" of table \"%s\" is empty string (replaced by NULL).",
212 SPI_fname(tupdesc, attnum), relname);
213 }
214 }
215 }
216 }
217 }
218
219 if (nresetcols > 0)
220 {
221 /* construct new tuple */
222 rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc,
223 nresetcols, resetcols,
224 values, nulls);
225 }
226
227 if (relname)
228 pfree(relname);
229 if (resetcols)
230 pfree(resetcols);
231 if (values)
232 pfree(values);
233 if (nulls)
234 pfree(nulls);
235
236 return PointerGetDatum(rettuple);
237 }
238
239 /*
240 * Detects NULL in type text based fields and replaces them by empty string
241 */
242 Datum
orafce_replace_null_strings(PG_FUNCTION_ARGS)243 orafce_replace_null_strings(PG_FUNCTION_ARGS)
244 {
245 TriggerData *trigdata = (TriggerData *) fcinfo->context;
246 HeapTuple rettuple = NULL;
247 TupleDesc tupdesc;
248 int *resetcols = NULL;
249 Datum *values = NULL;
250 bool *nulls = NULL;
251 Oid prev_typid = InvalidOid;
252 bool is_string = false;
253 int nresetcols = 0;
254 int attnum;
255 bool raise_warning = false;
256 char *relname = NULL;
257
258 trigger_sanity_check(fcinfo, "replace_null_strings");
259 raise_warning = should_raise_warnings(fcinfo);
260
261 rettuple = get_rettuple(fcinfo);
262
263 /* return fast when there are not any NULL */
264 if (!HeapTupleHasNulls(rettuple))
265 return PointerGetDatum(rettuple);
266
267 tupdesc = trigdata->tg_relation->rd_att;
268
269 /* iterate over record's fields */
270 for (attnum = 1; attnum <= tupdesc->natts; attnum++)
271 {
272 Oid typid;
273
274 /* simple cache - lot of time columns with same type is side by side */
275 typid = SPI_gettypeid(tupdesc, attnum);
276 if (typid != prev_typid)
277 {
278 TYPCATEGORY category;
279 bool ispreferred;
280 Oid base_typid;
281
282 base_typid = getBaseType(typid);
283 get_type_category_preferred(base_typid, &category, &ispreferred);
284
285 is_string = (category == TYPCATEGORY_STRING);
286 prev_typid = typid;
287 }
288
289 if (is_string)
290 {
291 bool isnull;
292
293 (void) SPI_getbinval(rettuple, tupdesc, attnum, &isnull);
294 if (isnull)
295 {
296 if (!resetcols)
297 {
298 /* lazy allocation of dynamic memory */
299 resetcols = palloc0(tupdesc->natts * sizeof(int));
300 nulls = palloc0(tupdesc->natts * sizeof(bool));
301 values = palloc0(tupdesc->natts * sizeof(Datum));
302 }
303
304 resetcols[nresetcols] = attnum;
305 values[nresetcols] = PointerGetDatum(cstring_to_text_with_len("", 0));
306 nulls[nresetcols++] = false;
307
308 if (raise_warning)
309 {
310 if (!relname)
311 relname = SPI_getrelname(trigdata->tg_relation);
312
313 elog(WARNING,
314 "Field \"%s\" of table \"%s\" is NULL (replaced by '').",
315 SPI_fname(tupdesc, attnum), relname);
316 }
317 }
318 }
319 }
320
321 if (nresetcols > 0)
322 {
323 /* construct new tuple */
324 rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc,
325 nresetcols, resetcols,
326 values, nulls);
327 }
328
329 if (relname)
330 pfree(relname);
331 if (resetcols)
332 pfree(resetcols);
333 if (values)
334 pfree(values);
335 if (nulls)
336 pfree(nulls);
337
338 return PointerGetDatum(rettuple);
339 }
340