2
* refint.c -- set of functions to define referential integrity
3
* constraints using general triggers.
6
#include "executor/spi.h" /* this is what you need to work with SPI */
7
#include "commands/trigger.h" /* -"- and triggers */
11
extern Datum check_primary_key(PG_FUNCTION_ARGS);
12
extern Datum check_foreign_key(PG_FUNCTION_ARGS);
22
static EPlan *FPlans = NULL;
23
static int nFPlans = 0;
24
static EPlan *PPlans = NULL;
25
static int nPPlans = 0;
27
static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans);
30
* check_primary_key () -- check that key in tuple being inserted/updated
31
* references existing tuple in "primary" table.
32
* Though it's called without args You have to specify referenced
33
* table/keys while creating trigger: key field names in triggered table,
34
* referenced table name, referenced key field names:
36
* check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2').
39
PG_FUNCTION_INFO_V1(check_primary_key);
42
check_primary_key(PG_FUNCTION_ARGS)
44
TriggerData *trigdata = (TriggerData *) fcinfo->context;
45
Trigger *trigger; /* to get trigger name */
46
int nargs; /* # of args specified in CREATE TRIGGER */
47
char **args; /* arguments: column names and table name */
48
int nkeys; /* # of key columns (= nargs / 2) */
49
Datum *kvals; /* key values */
50
char *relname; /* referenced relation name */
51
Relation rel; /* triggered relation */
52
HeapTuple tuple = NULL; /* tuple to return */
53
TupleDesc tupdesc; /* tuple description */
54
EPlan *plan; /* prepared plan */
55
Oid *argtypes = NULL; /* key types to prepare execution plan */
56
bool isnull; /* to know is some column NULL or not */
57
char ident[2 * NAMEDATALEN]; /* to identify myself */
62
elog(DEBUG4, "check_primary_key: Enter Function");
66
* Some checks first...
69
/* Called by trigger manager ? */
70
if (!CALLED_AS_TRIGGER(fcinfo))
72
elog(ERROR, "check_primary_key: not fired by trigger manager");
74
/* Should be called for ROW trigger */
75
if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
77
elog(ERROR, "check_primary_key: can't process STATEMENT events");
79
/* If INSERTion then must check Tuple to being inserted */
80
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
81
tuple = trigdata->tg_trigtuple;
83
/* Not should be called for DELETE */
84
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
86
elog(ERROR, "check_primary_key: can't process DELETE events");
88
/* If UPDATion the must check new Tuple, not old one */
90
tuple = trigdata->tg_newtuple;
92
trigger = trigdata->tg_trigger;
93
nargs = trigger->tgnargs;
94
args = trigger->tgargs;
96
if (nargs % 2 != 1) /* odd number of arguments! */
98
elog(ERROR, "check_primary_key: odd number of arguments should be specified");
101
relname = args[nkeys];
102
rel = trigdata->tg_relation;
103
tupdesc = rel->rd_att;
105
/* Connect to SPI manager */
106
if ((ret = SPI_connect()) < 0)
108
elog(ERROR, "check_primary_key: SPI_connect returned %d", ret);
111
* We use SPI plan preparation feature, so allocate space to place key
114
kvals = (Datum *) palloc(nkeys * sizeof(Datum));
117
* Construct ident string as TriggerName $ TriggeredRelationId and try
118
* to find prepared execution plan.
120
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
121
plan = find_plan(ident, &PPlans, &nPPlans);
123
/* if there is no plan then allocate argtypes for preparation */
124
if (plan->nplans <= 0)
125
argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
127
/* For each column in key ... */
128
for (i = 0; i < nkeys; i++)
130
/* get index of column in tuple */
131
int fnumber = SPI_fnumber(tupdesc, args[i]);
133
/* Bad guys may give us un-existing column in CREATE TRIGGER */
136
(errcode(ERRCODE_UNDEFINED_COLUMN),
137
errmsg("there is no attribute \"%s\" in relation \"%s\"",
138
args[i], SPI_getrelname(rel))));
140
/* Well, get binary (in internal format) value of column */
141
kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull);
144
* If it's NULL then nothing to do! DON'T FORGET call SPI_finish
145
* ()! DON'T FORGET return tuple! Executor inserts tuple you're
146
* returning! If you return NULL then nothing will be inserted!
151
return PointerGetDatum(tuple);
154
if (plan->nplans <= 0) /* Get typeId of column */
155
argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
159
* If we have to prepare plan ...
161
if (plan->nplans <= 0)
167
* Construct query: SELECT 1 FROM _referenced_relation_ WHERE
168
* Pkey1 = $1 [AND Pkey2 = $2 [...]]
170
snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
171
for (i = 0; i < nkeys; i++)
173
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s",
174
args[i + nkeys + 1], i + 1, (i < nkeys - 1) ? "and " : "");
177
/* Prepare plan for query */
178
pplan = SPI_prepare(sql, nkeys, argtypes);
181
elog(ERROR, "check_primary_key: SPI_prepare returned %d", SPI_result);
184
* Remember that SPI_prepare places plan in current memory context
185
* - so, we have to save plan in Top memory context for latter
188
pplan = SPI_saveplan(pplan);
191
elog(ERROR, "check_primary_key: SPI_saveplan returned %d", SPI_result);
192
plan->splan = (void **) malloc(sizeof(void *));
193
*(plan->splan) = pplan;
198
* Ok, execute prepared plan.
200
ret = SPI_execp(*(plan->splan), kvals, NULL, 1);
201
/* we have no NULLs - so we pass ^^^^ here */
205
elog(ERROR, "check_primary_key: SPI_execp returned %d", ret);
208
* If there are no tuples returned by SELECT then ...
210
if (SPI_processed == 0)
212
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
213
errmsg("tuple references non-existent key"),
214
errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname)));
218
return PointerGetDatum(tuple);
222
* check_foreign_key () -- check that key in tuple being deleted/updated
223
* is not referenced by tuples in "foreign" table(s).
224
* Though it's called without args You have to specify (while creating trigger):
225
* number of references, action to do if key referenced
226
* ('restrict' | 'setnull' | 'cascade'), key field names in triggered
227
* ("primary") table and referencing table(s)/keys:
229
* check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2',
230
* 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22').
233
PG_FUNCTION_INFO_V1(check_foreign_key);
236
check_foreign_key(PG_FUNCTION_ARGS)
238
TriggerData *trigdata = (TriggerData *) fcinfo->context;
239
Trigger *trigger; /* to get trigger name */
240
int nargs; /* # of args specified in CREATE TRIGGER */
241
char **args; /* arguments: as described above */
243
int nrefs; /* number of references (== # of plans) */
244
char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
245
int nkeys; /* # of key columns */
246
Datum *kvals; /* key values */
247
char *relname; /* referencing relation name */
248
Relation rel; /* triggered relation */
249
HeapTuple trigtuple = NULL; /* tuple to being changed */
250
HeapTuple newtuple = NULL; /* tuple to return */
251
TupleDesc tupdesc; /* tuple description */
252
EPlan *plan; /* prepared plan(s) */
253
Oid *argtypes = NULL; /* key types to prepare execution plan */
254
bool isnull; /* to know is some column NULL or not */
255
bool isequal = true; /* are keys in both tuples equal (in
257
char ident[2 * NAMEDATALEN]; /* to identify myself */
264
elog(DEBUG4, "check_foreign_key: Enter Function");
268
* Some checks first...
271
/* Called by trigger manager ? */
272
if (!CALLED_AS_TRIGGER(fcinfo))
274
elog(ERROR, "check_foreign_key: not fired by trigger manager");
276
/* Should be called for ROW trigger */
277
if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
279
elog(ERROR, "check_foreign_key: can't process STATEMENT events");
281
/* Not should be called for INSERT */
282
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
284
elog(ERROR, "check_foreign_key: can't process INSERT events");
286
/* Have to check tg_trigtuple - tuple being deleted */
287
trigtuple = trigdata->tg_trigtuple;
290
* But if this is UPDATE then we have to return tg_newtuple. Also, if
291
* key in tg_newtuple is the same as in tg_trigtuple then nothing to
295
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
297
newtuple = trigdata->tg_newtuple;
300
trigger = trigdata->tg_trigger;
301
nargs = trigger->tgnargs;
302
args = trigger->tgargs;
304
if (nargs < 5) /* nrefs, action, key, Relation, key - at
307
elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
309
nrefs = pg_atoi(args[0], sizeof(int), 0);
312
elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
313
action = tolower((unsigned char) *(args[1]));
314
if (action != 'r' && action != 'c' && action != 's')
316
elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
319
nkeys = (nargs - nrefs) / (nrefs + 1);
320
if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
322
elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
325
rel = trigdata->tg_relation;
326
tupdesc = rel->rd_att;
328
/* Connect to SPI manager */
329
if ((ret = SPI_connect()) < 0)
331
elog(ERROR, "check_foreign_key: SPI_connect returned %d", ret);
334
* We use SPI plan preparation feature, so allocate space to place key
337
kvals = (Datum *) palloc(nkeys * sizeof(Datum));
340
* Construct ident string as TriggerName $ TriggeredRelationId and try
341
* to find prepared execution plan(s).
343
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
344
plan = find_plan(ident, &FPlans, &nFPlans);
346
/* if there is no plan(s) then allocate argtypes for preparation */
347
if (plan->nplans <= 0)
348
argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
351
* else - check that we have exactly nrefs plan(s) ready
353
else if (plan->nplans != nrefs)
355
elog(ERROR, "%s: check_foreign_key: # of plans changed in meantime",
358
/* For each column in key ... */
359
for (i = 0; i < nkeys; i++)
361
/* get index of column in tuple */
362
int fnumber = SPI_fnumber(tupdesc, args[i]);
364
/* Bad guys may give us un-existing column in CREATE TRIGGER */
367
(errcode(ERRCODE_UNDEFINED_COLUMN),
368
errmsg("there is no attribute \"%s\" in relation \"%s\"",
369
args[i], SPI_getrelname(rel))));
371
/* Well, get binary (in internal format) value of column */
372
kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
375
* If it's NULL then nothing to do! DON'T FORGET call SPI_finish
376
* ()! DON'T FORGET return tuple! Executor inserts tuple you're
377
* returning! If you return NULL then nothing will be inserted!
382
return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
386
* If UPDATE then get column value from new tuple being inserted
387
* and compare is this the same as old one. For the moment we use
388
* string presentation of values...
390
if (newtuple != NULL)
392
char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
395
/* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
398
elog(ERROR, "check_foreign_key: SPI_getvalue returned %d", SPI_result);
399
newval = SPI_getvalue(newtuple, tupdesc, fnumber);
400
if (newval == NULL || strcmp(oldval, newval) != 0)
404
if (plan->nplans <= 0) /* Get typeId of column */
405
argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
412
* If we have to prepare plans ...
414
if (plan->nplans <= 0)
420
plan->splan = (void **) malloc(nrefs * sizeof(void *));
422
for (r = 0; r < nrefs; r++)
427
* For 'R'estrict action we construct SELECT query:
430
* FROM _referencing_relation_
431
* WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
433
* to check is tuple referenced or not.
438
snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
441
* For 'C'ascade action we construct DELETE query
444
* FROM _referencing_relation_
445
* WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
447
* to delete all referencing tuples.
452
* Max : Cascade with UPDATE query i create update query that
453
* updates new key values in referenced tables
457
else if (action == 'c')
465
snprintf(sql, sizeof(sql), "update %s set ", relname);
466
for (k = 1; k <= nkeys; k++)
468
int is_char_type = 0;
471
fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
472
nv = SPI_getvalue(newtuple, tupdesc, fn);
473
type = SPI_gettype(tupdesc, fn);
475
if ((strcmp(type, "text") && strcmp(type, "varchar") &&
476
strcmp(type, "char") && strcmp(type, "bpchar") &&
477
strcmp(type, "date") && strcmp(type, "timestamp")) == 0)
480
elog(DEBUG4, "check_foreign_key Debug value %s type %s %d",
481
nv, type, is_char_type);
485
* is_char_type =1 i set ' ' for define a new
488
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
490
args2[k], (is_char_type > 0) ? "'" : "",
491
nv, (is_char_type > 0) ? "'" : "", (k < nkeys) ? ", " : "");
494
strcat(sql, " where ");
499
snprintf(sql, sizeof(sql), "delete from %s where ", relname);
504
* For 'S'etnull action we construct UPDATE query - UPDATE
505
* _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]]
506
* WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key
507
* columns in all referencing tuples to NULL.
509
else if (action == 's')
511
snprintf(sql, sizeof(sql), "update %s set ", relname);
512
for (i = 1; i <= nkeys; i++)
514
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
516
args2[i], (i < nkeys) ? ", " : "");
518
strcat(sql, " where ");
521
/* Construct WHERE qual */
522
for (i = 1; i <= nkeys; i++)
524
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s",
525
args2[i], i, (i < nkeys) ? "and " : "");
528
/* Prepare plan for query */
529
pplan = SPI_prepare(sql, nkeys, argtypes);
532
elog(ERROR, "check_foreign_key: SPI_prepare returned %d", SPI_result);
535
* Remember that SPI_prepare places plan in current memory
536
* context - so, we have to save plan in Top memory context
539
pplan = SPI_saveplan(pplan);
542
elog(ERROR, "check_foreign_key: SPI_saveplan returned %d", SPI_result);
544
plan->splan[r] = pplan;
546
args2 += nkeys + 1; /* to the next relation */
548
plan->nplans = nrefs;
550
elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql);
555
* If UPDATE and key is not changed ...
557
if (newtuple != NULL && isequal)
560
return PointerGetDatum(newtuple);
564
* Ok, execute prepared plan(s).
566
for (r = 0; r < nrefs; r++)
569
* For 'R'estrict we may to execute plan for one tuple only, for
570
* other actions - for all tuples.
572
int tcount = (action == 'r') ? 1 : 0;
576
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
577
plan = find_plan(ident, &FPlans, &nFPlans);
578
ret = SPI_execp(plan->splan[r], kvals, NULL, tcount);
579
/* we have no NULLs - so we pass ^^^^ here */
583
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
584
errmsg("SPI_execp returned %d", ret)));
586
/* If action is 'R'estrict ... */
589
/* If there is tuple returned by SELECT then ... */
590
if (SPI_processed > 0)
592
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
593
errmsg("\"%s\": tuple is referenced in \"%s\"",
594
trigger->tgname, relname)));
598
#ifdef REFINT_VERBOSE
599
elog(NOTICE, "%s: %d tuple(s) of %s are %s",
600
trigger->tgname, SPI_processed, relname,
601
(action == 'c') ? "deleted" : "set to null");
604
args += nkeys + 1; /* to the next relation */
609
return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
613
find_plan(char *ident, EPlan ** eplan, int *nplans)
620
for (i = 0; i < *nplans; i++)
622
if (strcmp((*eplan)[i].ident, ident) == 0)
627
*eplan = (EPlan *) realloc(*eplan, (i + 1) * sizeof(EPlan));
632
newp = *eplan = (EPlan *) malloc(sizeof(EPlan));
636
newp->ident = (char *) malloc(strlen(ident) + 1);
637
strcpy(newp->ident, ident);
added
removed
contrib/spi/refint.c
