Datum /* have to return HeapTuple to Executor */
timetravel(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; /* to get trigger name */
int argc;
char **args; /* arguments */
int attnum[MaxAttrNum]; /* fnumbers of start/stop columns */
Datum oldtimeon,
oldtimeoff;
Datum newtimeon,
newtimeoff,
newuser,
nulltext;
Datum *cvals; /* column values */
char *cnulls; /* column nulls */
char *relname; /* triggered relation name */
Relation rel; /* triggered relation */
HeapTuple trigtuple;
HeapTuple newtuple = NULL;
HeapTuple rettuple;
TupleDesc tupdesc; /* tuple description */
int natts; /* # of attributes */
EPlan *plan; /* prepared plan */
char ident[2 * NAMEDATALEN];
bool isnull; /* to know is some column NULL or not */
bool isinsert = false;
int ret;
int i;
/*
* Some checks first...
*/
/* Called by trigger manager ? */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "timetravel: not fired by trigger manager");
/* Should be called for ROW trigger */
if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
elog(ERROR, "timetravel: must be fired for row");
/* Should be called BEFORE */
if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event))
elog(ERROR, "timetravel: must be fired before event");
/* INSERT ? */
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
isinsert = true;
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
newtuple = trigdata->tg_newtuple;
trigtuple = trigdata->tg_trigtuple;
rel = trigdata->tg_relation;
relname = SPI_getrelname(rel);
/* check if TT is OFF for this relation */
if (0 == findTTStatus(relname))
{
/* OFF - nothing to do */
pfree(relname);
return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple);
}
trigger = trigdata->tg_trigger;
argc = trigger->tgnargs;
if (argc != MinAttrNum && argc != MaxAttrNum)
elog(ERROR, "timetravel (%s): invalid (!= %d or %d) number of arguments %d",
relname, MinAttrNum, MaxAttrNum, trigger->tgnargs);
args = trigger->tgargs;
tupdesc = rel->rd_att;
natts = tupdesc->natts;
for (i = 0; i < MinAttrNum; i++)
{
attnum[i] = SPI_fnumber(tupdesc, args[i]);
if (attnum[i] < 0)
elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]);
if (SPI_gettypeid(tupdesc, attnum[i]) != ABSTIMEOID)
elog(ERROR, "timetravel (%s): attribute %s must be of abstime type",
relname, args[i]);
}
for (; i < argc; i++)
{
attnum[i] = SPI_fnumber(tupdesc, args[i]);
if (attnum[i] < 0)
elog(ERROR, "timetravel (%s): there is no attribute %s", relname, args[i]);
if (SPI_gettypeid(tupdesc, attnum[i]) != TEXTOID)
elog(ERROR, "timetravel (%s): attribute %s must be of text type",
relname, args[i]);
}
/* create fields containing name */
newuser = CStringGetTextDatum(GetUserNameFromId(GetUserId()));
nulltext = (Datum) NULL;
if (isinsert)
{ /* INSERT */
int chnattrs = 0;
int chattrs[MaxAttrNum];
Datum newvals[MaxAttrNum];
char newnulls[MaxAttrNum];
oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
if (isnull)
{
newvals[chnattrs] = GetCurrentAbsoluteTime();
newnulls[chnattrs] = ' ';
chattrs[chnattrs] = attnum[a_time_on];
chnattrs++;
}
oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
if (isnull)
{
if ((chnattrs == 0 && DatumGetInt32(oldtimeon) >= NOEND_ABSTIME) ||
(chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) >= NOEND_ABSTIME))
elog(ERROR, "timetravel (%s): %s is infinity", relname, args[a_time_on]);
newvals[chnattrs] = NOEND_ABSTIME;
newnulls[chnattrs] = ' ';
chattrs[chnattrs] = attnum[a_time_off];
chnattrs++;
}
else
{
if ((chnattrs == 0 && DatumGetInt32(oldtimeon) > DatumGetInt32(oldtimeoff)) ||
(chnattrs > 0 && DatumGetInt32(newvals[a_time_on]) > DatumGetInt32(oldtimeoff)))
elog(ERROR, "timetravel (%s): %s gt %s", relname, args[a_time_on], args[a_time_off]);
}
pfree(relname);
if (chnattrs <= 0)
return PointerGetDatum(trigtuple);
if (argc == MaxAttrNum)
{
/* clear update_user value */
newvals[chnattrs] = nulltext;
newnulls[chnattrs] = 'n';
chattrs[chnattrs] = attnum[a_upd_user];
chnattrs++;
/* clear delete_user value */
newvals[chnattrs] = nulltext;
newnulls[chnattrs] = 'n';
chattrs[chnattrs] = attnum[a_del_user];
chnattrs++;
/* set insert_user value */
newvals[chnattrs] = newuser;
newnulls[chnattrs] = ' ';
chattrs[chnattrs] = attnum[a_ins_user];
chnattrs++;
}
rettuple = SPI_modifytuple(rel, trigtuple, chnattrs, chattrs, newvals, newnulls);
return PointerGetDatum(rettuple);
/* end of INSERT */
}
/* UPDATE/DELETE: */
oldtimeon = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_on], &isnull);
if (isnull)
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
oldtimeoff = SPI_getbinval(trigtuple, tupdesc, attnum[a_time_off], &isnull);
if (isnull)
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
/*
* If DELETE/UPDATE of tuple with stop_date neq INFINITY then say upper
* Executor to skip operation for this tuple
*/
if (newtuple != NULL)
{ /* UPDATE */
newtimeon = SPI_getbinval(newtuple, tupdesc, attnum[a_time_on], &isnull);
if (isnull)
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_on]);
newtimeoff = SPI_getbinval(newtuple, tupdesc, attnum[a_time_off], &isnull);
if (isnull)
elog(ERROR, "timetravel (%s): %s must be NOT NULL", relname, args[a_time_off]);
if (oldtimeon != newtimeon || oldtimeoff != newtimeoff)
elog(ERROR, "timetravel (%s): you cannot change %s and/or %s columns (use set_timetravel)",
relname, args[a_time_on], args[a_time_off]);
}
if (oldtimeoff != NOEND_ABSTIME)
{ /* current record is a deleted/updated record */
pfree(relname);
return PointerGetDatum(NULL);
}
newtimeoff = GetCurrentAbsoluteTime();
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog(ERROR, "timetravel (%s): SPI_connect returned %d", relname, ret);
/* Fetch tuple values and nulls */
cvals = (Datum *) palloc(natts * sizeof(Datum));
cnulls = (char *) palloc(natts * sizeof(char));
for (i = 0; i < natts; i++)
{
cvals[i] = SPI_getbinval(trigtuple, tupdesc, i + 1, &isnull);
cnulls[i] = (isnull) ? 'n' : ' ';
}
/* change date column(s) */
cvals[attnum[a_time_off] - 1] = newtimeoff; /* stop_date eq current date */
cnulls[attnum[a_time_off] - 1] = ' ';
if (!newtuple)
{ /* DELETE */
if (argc == MaxAttrNum)
{
cvals[attnum[a_del_user] - 1] = newuser; /* set delete user */
cnulls[attnum[a_del_user] - 1] = ' ';
}
}
/*
* Construct ident string as TriggerName $ TriggeredRelationId and try to
* find prepared execution plan.
*/
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
plan = find_plan(ident, &Plans, &nPlans);
/* if there is no plan ... */
if (plan->splan == NULL)
{
SPIPlanPtr pplan;
Oid *ctypes;
char sql[8192];
char separ = ' ';
/* allocate ctypes for preparation */
ctypes = (Oid *) palloc(natts * sizeof(Oid));
/*
* Construct query: INSERT INTO _relation_ VALUES ($1, ...)
*/
snprintf(sql, sizeof(sql), "INSERT INTO %s VALUES (", relname);
for (i = 1; i <= natts; i++)
{
ctypes[i - 1] = SPI_gettypeid(tupdesc, i);
if (!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */
{
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ, i);
separ = ',';
}
}
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")");
elog(DEBUG4, "timetravel (%s) update: sql: %s", relname, sql);
/* Prepare plan for query */
pplan = SPI_prepare(sql, natts, ctypes);
if (pplan == NULL)
elog(ERROR, "timetravel (%s): SPI_prepare returned %d", relname, SPI_result);
/*
* Remember that SPI_prepare places plan in current memory context -
* so, we have to save plan in Top memory context for later use.
*/
if (SPI_keepplan(pplan))
elog(ERROR, "timetravel (%s): SPI_keepplan failed", relname);
plan->splan = pplan;
}
/*
* Ok, execute prepared plan.
*/
ret = SPI_execp(plan->splan, cvals, cnulls, 0);
if (ret < 0)
elog(ERROR, "timetravel (%s): SPI_execp returned %d", relname, ret);
/* Tuple to return to upper Executor ... */
if (newtuple)
{ /* UPDATE */
int chnattrs = 0;
int chattrs[MaxAttrNum];
Datum newvals[MaxAttrNum];
char newnulls[MaxAttrNum];
newvals[chnattrs] = newtimeoff;
newnulls[chnattrs] = ' ';
chattrs[chnattrs] = attnum[a_time_on];
chnattrs++;
newvals[chnattrs] = NOEND_ABSTIME;
newnulls[chnattrs] = ' ';
chattrs[chnattrs] = attnum[a_time_off];
chnattrs++;
if (argc == MaxAttrNum)
{
/* set update_user value */
newvals[chnattrs] = newuser;
newnulls[chnattrs] = ' ';
chattrs[chnattrs] = attnum[a_upd_user];
chnattrs++;
/* clear delete_user value */
newvals[chnattrs] = nulltext;
newnulls[chnattrs] = 'n';
chattrs[chnattrs] = attnum[a_del_user];
chnattrs++;
/* set insert_user value */
newvals[chnattrs] = nulltext;
newnulls[chnattrs] = 'n';
chattrs[chnattrs] = attnum[a_ins_user];
chnattrs++;
}
rettuple = SPI_modifytuple(rel, newtuple, chnattrs, chattrs, newvals, newnulls);
/*
* SPI_copytuple allocates tmptuple in upper executor context - have
* to free allocation using SPI_pfree
*/
/* SPI_pfree(tmptuple); */
}
else
/* DELETE case */
rettuple = trigtuple;
SPI_finish(); /* don't forget say Bye to SPI mgr */
pfree(relname);
return PointerGetDatum(rettuple);
}
/* prepare(query="select * from foo")
* prepare(query="select * from foo where bar = $1", params=["text"])
* prepare(query="select * from foo where bar = $1", params=["text"], limit=5)
*/
PyObject *
PLy_spi_prepare(PyObject *self, PyObject *args)
{
PLyPlanObject *plan;
PyObject *list = NULL;
PyObject *volatile optr = NULL;
char *query;
volatile MemoryContext oldcontext;
volatile ResourceOwner oldowner;
volatile int nargs;
if (!PyArg_ParseTuple(args, "s|O", &query, &list))
return NULL;
if (list && (!PySequence_Check(list)))
{
PLy_exception_set(PyExc_TypeError,
"second argument of plpy.prepare must be a sequence");
return NULL;
}
if ((plan = (PLyPlanObject *) PLy_plan_new()) == NULL)
return NULL;
nargs = list ? PySequence_Length(list) : 0;
plan->nargs = nargs;
plan->types = nargs ? PLy_malloc(sizeof(Oid) * nargs) : NULL;
plan->values = nargs ? PLy_malloc(sizeof(Datum) * nargs) : NULL;
plan->args = nargs ? PLy_malloc(sizeof(PLyTypeInfo) * nargs) : NULL;
oldcontext = CurrentMemoryContext;
oldowner = CurrentResourceOwner;
PLy_spi_subtransaction_begin(oldcontext, oldowner);
PG_TRY();
{
int i;
/*
* the other loop might throw an exception, if PLyTypeInfo member
* isn't properly initialized the Py_DECREF(plan) will go boom
*/
for (i = 0; i < nargs; i++)
{
PLy_typeinfo_init(&plan->args[i]);
plan->values[i] = PointerGetDatum(NULL);
}
for (i = 0; i < nargs; i++)
{
char *sptr;
HeapTuple typeTup;
Oid typeId;
int32 typmod;
Form_pg_type typeStruct;
optr = PySequence_GetItem(list, i);
if (PyString_Check(optr))
sptr = PyString_AsString(optr);
else if (PyUnicode_Check(optr))
sptr = PLyUnicode_AsString(optr);
else
{
ereport(ERROR,
(errmsg("plpy.prepare: type name at ordinal position %d is not a string", i)));
sptr = NULL; /* keep compiler quiet */
}
/********************************************************
* Resolve argument type names and then look them up by
* oid in the system cache, and remember the required
*information for input conversion.
********************************************************/
parseTypeString(sptr, &typeId, &typmod);
typeTup = SearchSysCache1(TYPEOID,
ObjectIdGetDatum(typeId));
if (!HeapTupleIsValid(typeTup))
elog(ERROR, "cache lookup failed for type %u", typeId);
Py_DECREF(optr);
/*
* set optr to NULL, so we won't try to unref it again in case of
* an error
*/
optr = NULL;
plan->types[i] = typeId;
typeStruct = (Form_pg_type) GETSTRUCT(typeTup);
if (typeStruct->typtype != TYPTYPE_COMPOSITE)
PLy_output_datum_func(&plan->args[i], typeTup);
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("plpy.prepare does not support composite types")));
ReleaseSysCache(typeTup);
}
pg_verifymbstr(query, strlen(query), false);
plan->plan = SPI_prepare(query, plan->nargs, plan->types);
if (plan->plan == NULL)
elog(ERROR, "SPI_prepare failed: %s",
SPI_result_code_string(SPI_result));
/* transfer plan from procCxt to topCxt */
if (SPI_keepplan(plan->plan))
elog(ERROR, "SPI_keepplan failed");
PLy_spi_subtransaction_commit(oldcontext, oldowner);
}
PG_CATCH();
{
Py_DECREF(plan);
Py_XDECREF(optr);
PLy_spi_subtransaction_abort(oldcontext, oldowner);
return NULL;
}
PG_END_TRY();
Assert(plan->plan != NULL);
return (PyObject *) plan;
}
Datum
check_primary_key(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; /* to get trigger name */
int nargs; /* # of args specified in CREATE TRIGGER */
char **args; /* arguments: column names and table name */
int nkeys; /* # of key columns (= nargs / 2) */
Datum *kvals; /* key values */
char *relname; /* referenced relation name */
Relation rel; /* triggered relation */
HeapTuple tuple = NULL; /* tuple to return */
TupleDesc tupdesc; /* tuple description */
EPlan *plan; /* prepared plan */
Oid *argtypes = NULL; /* key types to prepare execution plan */
bool isnull; /* to know is some column NULL or not */
char ident[2 * NAMEDATALEN]; /* to identify myself */
int ret;
int i;
#ifdef DEBUG_QUERY
elog(DEBUG4, "check_primary_key: Enter Function");
#endif
/*
* Some checks first...
*/
/* Called by trigger manager ? */
if (!CALLED_AS_TRIGGER(fcinfo))
/* internal error */
elog(ERROR, "check_primary_key: not fired by trigger manager");
/* Should be called for ROW trigger */
if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
/* internal error */
elog(ERROR, "check_primary_key: must be fired for row");
/* If INSERTion then must check Tuple to being inserted */
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
tuple = trigdata->tg_trigtuple;
/* Not should be called for DELETE */
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
/* internal error */
elog(ERROR, "check_primary_key: cannot process DELETE events");
/* If UPDATion the must check new Tuple, not old one */
else
tuple = trigdata->tg_newtuple;
trigger = trigdata->tg_trigger;
nargs = trigger->tgnargs;
args = trigger->tgargs;
if (nargs % 2 != 1) /* odd number of arguments! */
/* internal error */
elog(ERROR, "check_primary_key: odd number of arguments should be specified");
nkeys = nargs / 2;
relname = args[nkeys];
rel = trigdata->tg_relation;
tupdesc = rel->rd_att;
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
/* internal error */
elog(ERROR, "check_primary_key: SPI_connect returned %d", ret);
/*
* We use SPI plan preparation feature, so allocate space to place key
* values.
*/
kvals = (Datum *) palloc(nkeys * sizeof(Datum));
/*
* Construct ident string as TriggerName $ TriggeredRelationId and try to
* find prepared execution plan.
*/
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
plan = find_plan(ident, &PPlans, &nPPlans);
/* if there is no plan then allocate argtypes for preparation */
if (plan->nplans <= 0)
argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
/* For each column in key ... */
for (i = 0; i < nkeys; i++)
{
/* get index of column in tuple */
int fnumber = SPI_fnumber(tupdesc, args[i]);
/* Bad guys may give us un-existing column in CREATE TRIGGER */
if (fnumber < 0)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("there is no attribute \"%s\" in relation \"%s\"",
args[i], SPI_getrelname(rel))));
/* Well, get binary (in internal format) value of column */
kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull);
/*
* If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
* DON'T FORGET return tuple! Executor inserts tuple you're returning!
* If you return NULL then nothing will be inserted!
*/
if (isnull)
{
SPI_finish();
return PointerGetDatum(tuple);
}
if (plan->nplans <= 0) /* Get typeId of column */
argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
}
/*
* If we have to prepare plan ...
*/
if (plan->nplans <= 0)
{
SPIPlanPtr pplan;
char sql[8192];
/*
* Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 =
* $1 [AND Pkey2 = $2 [...]]
*/
snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
for (i = 0; i < nkeys; i++)
{
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s",
args[i + nkeys + 1], i + 1, (i < nkeys - 1) ? "and " : "");
}
/* Prepare plan for query */
pplan = SPI_prepare(sql, nkeys, argtypes);
if (pplan == NULL)
/* internal error */
elog(ERROR, "check_primary_key: SPI_prepare returned %d", SPI_result);
/*
* Remember that SPI_prepare places plan in current memory context -
* so, we have to save plan in Top memory context for later use.
*/
if (SPI_keepplan(pplan))
/* internal error */
elog(ERROR, "check_primary_key: SPI_keepplan failed");
plan->splan = (SPIPlanPtr *) malloc(sizeof(SPIPlanPtr));
*(plan->splan) = pplan;
plan->nplans = 1;
}
/*
* Ok, execute prepared plan.
*/
ret = SPI_execp(*(plan->splan), kvals, NULL, 1);
/* we have no NULLs - so we pass ^^^^ here */
if (ret < 0)
/* internal error */
elog(ERROR, "check_primary_key: SPI_execp returned %d", ret);
/*
* If there are no tuples returned by SELECT then ...
*/
if (SPI_processed == 0)
ereport(ERROR,
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
errmsg("tuple references non-existent key"),
errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname)));
SPI_finish();
return PointerGetDatum(tuple);
}
Datum
ttdummy(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; /* to get trigger name */
char **args; /* arguments */
int attnum[2]; /* fnumbers of start/stop columns */
Datum oldon,
oldoff;
Datum newon,
newoff;
Datum *cvals; /* column values */
char *cnulls; /* column nulls */
char *relname; /* triggered relation name */
Relation rel; /* triggered relation */
HeapTuple trigtuple;
HeapTuple newtuple = NULL;
HeapTuple rettuple;
TupleDesc tupdesc; /* tuple description */
int natts; /* # of attributes */
bool isnull; /* to know is some column NULL or not */
int ret;
int i;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "ttdummy: not fired by trigger manager");
if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
elog(ERROR, "ttdummy: must be fired for row");
if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event))
elog(ERROR, "ttdummy: must be fired before event");
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
elog(ERROR, "ttdummy: cannot process INSERT event");
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
newtuple = trigdata->tg_newtuple;
trigtuple = trigdata->tg_trigtuple;
rel = trigdata->tg_relation;
relname = SPI_getrelname(rel);
/* check if TT is OFF for this relation */
if (ttoff) /* OFF - nothing to do */
{
pfree(relname);
return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple);
}
trigger = trigdata->tg_trigger;
if (trigger->tgnargs != 2)
elog(ERROR, "ttdummy (%s): invalid (!= 2) number of arguments %d",
relname, trigger->tgnargs);
args = trigger->tgargs;
tupdesc = rel->rd_att;
natts = tupdesc->natts;
for (i = 0; i < 2; i++)
{
attnum[i] = SPI_fnumber(tupdesc, args[i]);
if (attnum[i] <= 0)
elog(ERROR, "ttdummy (%s): there is no attribute %s",
relname, args[i]);
if (SPI_gettypeid(tupdesc, attnum[i]) != INT4OID)
elog(ERROR, "ttdummy (%s): attribute %s must be of integer type",
relname, args[i]);
}
oldon = SPI_getbinval(trigtuple, tupdesc, attnum[0], &isnull);
if (isnull)
elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]);
oldoff = SPI_getbinval(trigtuple, tupdesc, attnum[1], &isnull);
if (isnull)
elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]);
if (newtuple != NULL) /* UPDATE */
{
newon = SPI_getbinval(newtuple, tupdesc, attnum[0], &isnull);
if (isnull)
elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]);
newoff = SPI_getbinval(newtuple, tupdesc, attnum[1], &isnull);
if (isnull)
elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]);
if (oldon != newon || oldoff != newoff)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("ttdummy (%s): you cannot change %s and/or %s columns (use set_ttdummy)",
relname, args[0], args[1])));
if (newoff != TTDUMMY_INFINITY)
{
pfree(relname); /* allocated in upper executor context */
return PointerGetDatum(NULL);
}
}
else if (oldoff != TTDUMMY_INFINITY) /* DELETE */
{
pfree(relname);
return PointerGetDatum(NULL);
}
newoff = DirectFunctionCall1(nextval, CStringGetTextDatum("ttdummy_seq"));
/* nextval now returns int64; coerce down to int32 */
newoff = Int32GetDatum((int32) DatumGetInt64(newoff));
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog(ERROR, "ttdummy (%s): SPI_connect returned %d", relname, ret);
/* Fetch tuple values and nulls */
cvals = (Datum *) palloc(natts * sizeof(Datum));
cnulls = (char *) palloc(natts * sizeof(char));
for (i = 0; i < natts; i++)
{
cvals[i] = SPI_getbinval((newtuple != NULL) ? newtuple : trigtuple,
tupdesc, i + 1, &isnull);
cnulls[i] = (isnull) ? 'n' : ' ';
}
/* change date column(s) */
if (newtuple) /* UPDATE */
{
cvals[attnum[0] - 1] = newoff; /* start_date eq current date */
cnulls[attnum[0] - 1] = ' ';
cvals[attnum[1] - 1] = TTDUMMY_INFINITY; /* stop_date eq INFINITY */
cnulls[attnum[1] - 1] = ' ';
}
else
/* DELETE */
{
cvals[attnum[1] - 1] = newoff; /* stop_date eq current date */
cnulls[attnum[1] - 1] = ' ';
}
/* if there is no plan ... */
if (splan == NULL)
{
SPIPlanPtr pplan;
Oid *ctypes;
char *query;
/* allocate space in preparation */
ctypes = (Oid *) palloc(natts * sizeof(Oid));
query = (char *) palloc(100 + 16 * natts);
/*
* Construct query: INSERT INTO _relation_ VALUES ($1, ...)
*/
sprintf(query, "INSERT INTO %s VALUES (", relname);
for (i = 1; i <= natts; i++)
{
sprintf(query + strlen(query), "$%d%s",
i, (i < natts) ? ", " : ")");
ctypes[i - 1] = SPI_gettypeid(tupdesc, i);
}
/* Prepare plan for query */
pplan = SPI_prepare(query, natts, ctypes);
if (pplan == NULL)
elog(ERROR, "ttdummy (%s): SPI_prepare returned %s", relname, SPI_result_code_string(SPI_result));
if (SPI_keepplan(pplan))
elog(ERROR, "ttdummy (%s): SPI_keepplan failed", relname);
splan = pplan;
}
ret = SPI_execp(splan, cvals, cnulls, 0);
if (ret < 0)
elog(ERROR, "ttdummy (%s): SPI_execp returned %d", relname, ret);
/* Tuple to return to upper Executor ... */
if (newtuple) /* UPDATE */
rettuple = SPI_modifytuple(rel, trigtuple, 1, &(attnum[1]), &newoff, NULL);
else /* DELETE */
rettuple = trigtuple;
SPI_finish(); /* don't forget say Bye to SPI mgr */
pfree(relname);
return PointerGetDatum(rettuple);
}
Datum
check_foreign_key(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; /* to get trigger name */
int nargs; /* # of args specified in CREATE TRIGGER */
char **args; /* arguments: as described above */
char **args_temp;
int nrefs; /* number of references (== # of plans) */
char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
int nkeys; /* # of key columns */
Datum *kvals; /* key values */
char *relname; /* referencing relation name */
Relation rel; /* triggered relation */
HeapTuple trigtuple = NULL; /* tuple to being changed */
HeapTuple newtuple = NULL; /* tuple to return */
TupleDesc tupdesc; /* tuple description */
EPlan *plan; /* prepared plan(s) */
Oid *argtypes = NULL; /* key types to prepare execution plan */
bool isnull; /* to know is some column NULL or not */
bool isequal = true; /* are keys in both tuples equal (in UPDATE) */
char ident[2 * NAMEDATALEN]; /* to identify myself */
int is_update = 0;
int ret;
int i,
r;
#ifdef DEBUG_QUERY
elog(DEBUG4, "check_foreign_key: Enter Function");
#endif
/*
* Some checks first...
*/
/* Called by trigger manager ? */
if (!CALLED_AS_TRIGGER(fcinfo))
/* internal error */
elog(ERROR, "check_foreign_key: not fired by trigger manager");
/* Should be called for ROW trigger */
if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
/* internal error */
elog(ERROR, "check_foreign_key: must be fired for row");
/* Not should be called for INSERT */
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
/* internal error */
elog(ERROR, "check_foreign_key: cannot process INSERT events");
/* Have to check tg_trigtuple - tuple being deleted */
trigtuple = trigdata->tg_trigtuple;
/*
* But if this is UPDATE then we have to return tg_newtuple. Also, if key
* in tg_newtuple is the same as in tg_trigtuple then nothing to do.
*/
is_update = 0;
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
{
newtuple = trigdata->tg_newtuple;
is_update = 1;
}
trigger = trigdata->tg_trigger;
nargs = trigger->tgnargs;
args = trigger->tgargs;
if (nargs < 5) /* nrefs, action, key, Relation, key - at
* least */
/* internal error */
elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
nrefs = pg_atoi(args[0], sizeof(int), 0);
if (nrefs < 1)
/* internal error */
elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
action = tolower((unsigned char) *(args[1]));
if (action != 'r' && action != 'c' && action != 's')
/* internal error */
elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
nargs -= 2;
args += 2;
nkeys = (nargs - nrefs) / (nrefs + 1);
if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
/* internal error */
elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
nargs + 2, nrefs);
rel = trigdata->tg_relation;
tupdesc = rel->rd_att;
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
/* internal error */
elog(ERROR, "check_foreign_key: SPI_connect returned %d", ret);
/*
* We use SPI plan preparation feature, so allocate space to place key
* values.
*/
kvals = (Datum *) palloc(nkeys * sizeof(Datum));
/*
* Construct ident string as TriggerName $ TriggeredRelationId and try to
* find prepared execution plan(s).
*/
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
plan = find_plan(ident, &FPlans, &nFPlans);
/* if there is no plan(s) then allocate argtypes for preparation */
if (plan->nplans <= 0)
argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
/*
* else - check that we have exactly nrefs plan(s) ready
*/
else if (plan->nplans != nrefs)
/* internal error */
elog(ERROR, "%s: check_foreign_key: # of plans changed in meantime",
trigger->tgname);
/* For each column in key ... */
for (i = 0; i < nkeys; i++)
{
/* get index of column in tuple */
int fnumber = SPI_fnumber(tupdesc, args[i]);
/* Bad guys may give us un-existing column in CREATE TRIGGER */
if (fnumber < 0)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("there is no attribute \"%s\" in relation \"%s\"",
args[i], SPI_getrelname(rel))));
/* Well, get binary (in internal format) value of column */
kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
/*
* If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
* DON'T FORGET return tuple! Executor inserts tuple you're returning!
* If you return NULL then nothing will be inserted!
*/
if (isnull)
{
SPI_finish();
return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
}
/*
* If UPDATE then get column value from new tuple being inserted and
* compare is this the same as old one. For the moment we use string
* presentation of values...
*/
if (newtuple != NULL)
{
char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
char *newval;
/* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
if (oldval == NULL)
/* internal error */
elog(ERROR, "check_foreign_key: SPI_getvalue returned %d", SPI_result);
newval = SPI_getvalue(newtuple, tupdesc, fnumber);
if (newval == NULL || strcmp(oldval, newval) != 0)
isequal = false;
}
if (plan->nplans <= 0) /* Get typeId of column */
argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
}
args_temp = args;
nargs -= nkeys;
args += nkeys;
/*
* If we have to prepare plans ...
*/
if (plan->nplans <= 0)
{
SPIPlanPtr pplan;
char sql[8192];
char **args2 = args;
plan->splan = (SPIPlanPtr *) malloc(nrefs * sizeof(SPIPlanPtr));
for (r = 0; r < nrefs; r++)
{
relname = args2[0];
/*---------
* For 'R'estrict action we construct SELECT query:
*
* SELECT 1
* FROM _referencing_relation_
* WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
*
* to check is tuple referenced or not.
*---------
*/
if (action == 'r')
snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
/*---------
* For 'C'ascade action we construct DELETE query
*
* DELETE
* FROM _referencing_relation_
* WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
*
* to delete all referencing tuples.
*---------
*/
/*
* Max : Cascade with UPDATE query i create update query that
* updates new key values in referenced tables
*/
else if (action == 'c')
{
if (is_update == 1)
{
int fn;
char *nv;
int k;
snprintf(sql, sizeof(sql), "update %s set ", relname);
for (k = 1; k <= nkeys; k++)
{
int is_char_type = 0;
char *type;
fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
nv = SPI_getvalue(newtuple, tupdesc, fn);
type = SPI_gettype(tupdesc, fn);
if ((strcmp(type, "text") && strcmp(type, "varchar") &&
strcmp(type, "char") && strcmp(type, "bpchar") &&
strcmp(type, "date") && strcmp(type, "timestamp")) == 0)
is_char_type = 1;
#ifdef DEBUG_QUERY
elog(DEBUG4, "check_foreign_key Debug value %s type %s %d",
nv, type, is_char_type);
#endif
/*
* is_char_type =1 i set ' ' for define a new value
*/
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
" %s = %s%s%s %s ",
args2[k], (is_char_type > 0) ? "'" : "",
nv, (is_char_type > 0) ? "'" : "", (k < nkeys) ? ", " : "");
is_char_type = 0;
}
strcat(sql, " where ");
}
else
/* DELETE */
snprintf(sql, sizeof(sql), "delete from %s where ", relname);
}
/*
* For 'S'etnull action we construct UPDATE query - UPDATE
* _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]]
* WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in
* all referencing tuples to NULL.
*/
else if (action == 's')
{
snprintf(sql, sizeof(sql), "update %s set ", relname);
for (i = 1; i <= nkeys; i++)
{
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
"%s = null%s",
args2[i], (i < nkeys) ? ", " : "");
}
strcat(sql, " where ");
}
/* Construct WHERE qual */
for (i = 1; i <= nkeys; i++)
{
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s",
args2[i], i, (i < nkeys) ? "and " : "");
}
/* Prepare plan for query */
pplan = SPI_prepare(sql, nkeys, argtypes);
if (pplan == NULL)
/* internal error */
elog(ERROR, "check_foreign_key: SPI_prepare returned %d", SPI_result);
/*
* Remember that SPI_prepare places plan in current memory context
* - so, we have to save plan in Top memory context for later use.
*/
if (SPI_keepplan(pplan))
/* internal error */
elog(ERROR, "check_foreign_key: SPI_keepplan failed");
plan->splan[r] = pplan;
args2 += nkeys + 1; /* to the next relation */
}
plan->nplans = nrefs;
#ifdef DEBUG_QUERY
elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql);
#endif
}
/*
* If UPDATE and key is not changed ...
*/
if (newtuple != NULL && isequal)
{
SPI_finish();
return PointerGetDatum(newtuple);
}
/*
* Ok, execute prepared plan(s).
*/
for (r = 0; r < nrefs; r++)
{
/*
* For 'R'estrict we may to execute plan for one tuple only, for other
* actions - for all tuples.
*/
int tcount = (action == 'r') ? 1 : 0;
relname = args[0];
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
plan = find_plan(ident, &FPlans, &nFPlans);
ret = SPI_execp(plan->splan[r], kvals, NULL, tcount);
/* we have no NULLs - so we pass ^^^^ here */
if (ret < 0)
ereport(ERROR,
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
errmsg("SPI_execp returned %d", ret)));
/* If action is 'R'estrict ... */
if (action == 'r')
{
/* If there is tuple returned by SELECT then ... */
if (SPI_processed > 0)
ereport(ERROR,
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
errmsg("\"%s\": tuple is referenced in \"%s\"",
trigger->tgname, relname)));
}
else
{
#ifdef REFINT_VERBOSE
elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s",
trigger->tgname, SPI_processed, relname,
(action == 'c') ? "deleted" : "set to null");
#endif
}
args += nkeys + 1; /* to the next relation */
}
SPI_finish();
return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
}
