Last active
May 29, 2018 05:23
-
-
Save kmoppel/b0fed6c5a29325e81ecf65193859a4b9 to your computer and use it in GitHub Desktop.
Simple Postgres audit trigger written in C. Tested with PG 10.4
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#include "postgres.h" | |
#include "access/htup_details.h" | |
#include "catalog/pg_type.h" | |
#include "commands/trigger.h" | |
#include "executor/spi.h" | |
#include "miscadmin.h" | |
#include "utils/builtins.h" | |
#include "utils/rel.h" | |
#include "utils/datetime.h" | |
PG_MODULE_MAGIC; | |
PG_FUNCTION_INFO_V1(set_audit_columns_if_empty); | |
Datum | |
set_audit_columns_if_empty(PG_FUNCTION_ARGS) | |
{ | |
TriggerData *trigdata = (TriggerData *) fcinfo->context; | |
Trigger *trigger; /* to get trigger name */ | |
int nargs; /* # of arguments */ | |
bool newnull = false; /* null flag */ | |
char **args; /* arguments */ | |
char *relname; /* triggered relation name */ | |
char *valbuf; | |
Relation rel; /* triggered relation */ | |
HeapTuple rettuple = NULL; | |
TupleDesc tupdesc; /* tuple description */ | |
int attnum; | |
Datum newval; /* for new timestamp */ | |
/* sanity checks from autoinc.c */ | |
if (!CALLED_AS_TRIGGER(fcinfo)) | |
/* internal error */ | |
elog(ERROR, "set_audit_columns_if_empty: not fired by trigger manager"); | |
if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) | |
/* internal error */ | |
elog(ERROR, "set_audit_columns_if_empty: must be fired for row"); | |
if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) | |
/* internal error */ | |
elog(ERROR, "set_audit_columns_if_empty: must be fired before event"); | |
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) | |
rettuple = trigdata->tg_trigtuple; | |
else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) | |
rettuple = trigdata->tg_newtuple; | |
else | |
/* internal error */ | |
elog(ERROR, "set_audit_columns_if_empty: cannot process DELETE events"); | |
rel = trigdata->tg_relation; | |
tupdesc = rel->rd_att; | |
// audit field #1 - last_modified_on | |
attnum = SPI_fnumber(tupdesc, "last_modified_on"); | |
if (attnum <= 0) | |
ereport(ERROR, | |
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), | |
errmsg("relation \"%d\" has no attribute \"%s\"", rel->rd_id, "last_modified_on"))); | |
valbuf = (char*)SPI_getvalue(rettuple, tupdesc, attnum); | |
if (valbuf == NULL) { | |
newval = GetCurrentTimestamp(); | |
rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc, | |
1, &attnum, &newval, &newnull); | |
} | |
// audit field #2 - last_modified_by | |
attnum = SPI_fnumber(tupdesc, "last_modified_by"); | |
if (attnum <= 0) | |
ereport(ERROR, | |
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), | |
errmsg("relation \"%d\" has no attribute \"%s\"", rel->rd_id, "last_modified_by"))); | |
valbuf = (char*)SPI_getvalue(rettuple, tupdesc, attnum); | |
if (valbuf == NULL) { | |
newval = CStringGetTextDatum(GetUserNameFromId(GetUserId(), false)); | |
rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc, | |
1, &attnum, &newval, &newnull); | |
} | |
return PointerGetDatum(rettuple); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment