Audit trigger c

=== The trigger ===
CREATE SCHEMA log;
CREATE TABLE log.audits
(
 id bigserial NOT NULL,
 owner_role character varying(64) NOT NULL,
 owner_id character varying(64) NOT NULL,
 operation character(1) NOT NULL,
 fields character varying(64)[],
 data text[],
 transaction bigint,
 CONSTRAINT audits_pkey PRIMARY KEY (id)
);
CREATE OR REPLACE FUNCTION auditc() RETURNS trigger AS
 'audit.so', 'auditc'
LANGUAGE c VOLATILE COST 1;
=== Demo / example code ===
CREATE TABLE teste_table
(
 id serial NOT NULL,
 teste_char VARCHAR(16) NOT NULL,
 teste_int integer NOT NULL,
 CONSTRAINT teste_table_pkey PRIMARY KEY (id)
);
CREATE TRIGGER audit_teste_table
 BEFORE INSERT OR DELETE OR UPDATE ON teste_table
 FOR EACH ROW EXECUTE PROCEDURE auditc();
insert into teste_table (id, teste_char, teste_int) values(10, 'Hello', 123);
insert into teste_table (id, teste_char, teste_int) values(20, 'Good', 456);
update teste_table set teste_char = 'World' where id = 10;
update teste_table set teste_char = 'Bye' where id = 20;
select id, owner_role, owner_id, operation, data, fields from log.audits where owner_role = 'public.teste_table' order by owner_id;
Result:
 id | owner_role | owner_id | operation | data | fields 
-------+--------------------+----------+-----------+---------------+---------------------------
 1 | public.teste_table | 10 | I | {123,Hello,10} | {teste_int,teste_char,id}
 3 | public.teste_table | 10 | U | {World} | {teste_char}
 2 | public.teste_table | 20 | I | {456,Good,20} | {teste_int,teste_char,id}
 4 | public.teste_table | 20 | U | {Bye} | {teste_char}
=== The Lib audit.c ===
#include "postgres.h"
#include "executor/spi.h"
#include "commands/trigger.h"
#include "utils/array.h"
#include "utils/rel.h"
#include "utils/builtins.h"
#include "catalog/pg_type.h"
#include "access/htup_details.h"
#ifdef PG_MODULE_MAGIC
 PG_MODULE_MAGIC;
#endif
//#define DEBUGAUDIT
#define OID_VARCHAR_ARRAY 1015
extern Datum auditc(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(auditc);
/**
 * Structs
 **/
typedef char* string;
typedef struct {
 string* fields;
 string* data;
 string* pks;
 uint32 pk_length;
 int size;
} diffret;
typedef struct {
 string key;
 uint32 pk_length;
 string *values;
} entryset;
static entryset* primaryKeys = 0;
static int tableUsed = 0;
static int tableAmount = 0;
/**
 * Audit function
 **/
Datum
auditc(PG_FUNCTION_ARGS)
{
 if (SPI_connect() == SPI_OK_CONNECT)
 {
/** **************************** BEGIN singleton *****************************************/
 if (!tableAmount)
 {
 SPIPlanPtr prep = SPI_prepare("select count(tablename) from pg_tables where schemaname = 'public'", 0, NULL);
 if (prep)
 {
 if (SPI_execute_plan(prep, NULL, NULL, true, 0))
 {
 uint32 pk_length = SPI_processed;
 if (pk_length > 0)
 {
 tableAmount = atoi(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1));
 }
 }
 }
 primaryKeys = malloc(tableAmount * sizeof(entryset));
 }
/** ---------------------------- END singleton ---------------------------- **/
 TriggerData *trigdata = (TriggerData *) fcinfo->context;
 HeapTuple oldtuple;
 HeapTuple newtuple;
#ifdef DEBUGAUDIT
 display(trigdata);
#endif
 char operation;
 if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
 {
 operation = 'I';
 oldtuple = 0;
 newtuple = trigdata->tg_trigtuple;
 } else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
 {
 operation = 'U';
 oldtuple = trigdata->tg_trigtuple;
 newtuple = trigdata->tg_newtuple;
 } else {
 operation = 'D';
 oldtuple = trigdata->tg_trigtuple;
 newtuple = trigdata->tg_trigtuple;
 }
 diffret ret;
 TupleDesc description = trigdata->tg_relation->rd_att;
 ret.size = 0;
 ret.pks = 0;
 ret.pk_length = 0;
 uint32 colunsSize = HeapTupleHeaderGetNatts(newtuple->t_data);
 if (colunsSize > 0)
 {
 int pos;
 ret.fields = SPI_palloc(colunsSize * sizeof(string));
 ret.data = SPI_palloc(colunsSize * sizeof(string));
 string *pks = 0;
/** **************************** GET PKS *****************************************/
#ifdef DEBUGAUDIT
 displayconstraints(trigdata->tg_relation);
 elog(INFO, "_____CHECK_PRIMARY_KEYS________");
#endif
/** **************************** CHECK PKS IN singleton *****************************************/
 {
 int row;
 for (row = 0; row < tableUsed; row++)
 {
#ifdef DEBUGAUDIT
 elog(INFO, "pk: %s", primaryKeys[row].key);
#endif
 if (strcmp(primaryKeys[row].key, SPI_getrelname(trigdata->tg_relation)) == 0)
 {
 ret.pk_length = primaryKeys[row].pk_length;
 pks = primaryKeys[row].values;
 }
 }
 }
/** ---------------------------- END CHECK PKS IN singleton ---------------------------- **/
/** **************************** CHECK PKS IN Data BASE *****************************************/
 if (!pks)
 {
#ifdef DEBUGAUDIT
 elog(INFO, "_____LOADING_PRIMARY_KEYS______");
#endif
 Oid oids[1] = {VARCHAROID};
 SPIPlanPtr prep = SPI_prepare("SELECT a.attname AS keys FROM pg_class c, pg_attribute a, pg_index i"
 " WHERE c.relname = $1 AND c.oid=i.indrelid AND a.attnum > 0"
 " AND a.attrelid = i.indexrelid AND i.indisprimary='t' ORDER BY a.attname ASC", 1, oids);
 if (prep)
 {
 Datum * parameters = (Datum *) SPI_palloc(sizeof(Datum));
 parameters[0] = CStringGetDatum(cstring_to_text(SPI_getrelname(trigdata->tg_relation)));
 if (SPI_execute_plan(prep, parameters, NULL, true, 0))
 {
 ret.pk_length = SPI_processed;
 if (ret.pk_length > 0)
 {
 pks = malloc(ret.pk_length * sizeof(string));
 int row;
 for (row = 0; row < ret.pk_length; row++)
 {
 string value = SPI_getvalue(SPI_tuptable->vals[row], SPI_tuptable->tupdesc, 1);
 int size = strlen(value) + 1;
 pks[row] = malloc(size);
 memcpy( pks[row], value, size);
 SPI_pfree(value);
#ifdef DEBUGAUDIT
 elog(INFO, "pk: %s", pks[row]);
#endif
 }
 }
 if (tableUsed < tableAmount)
 {
 char* tableName = SPI_getrelname(trigdata->tg_relation);
 int size = strlen(tableName) + 1;
 primaryKeys[tableUsed].key = malloc(size);
 memcpy( primaryKeys[tableUsed].key, tableName, size);
 primaryKeys[tableUsed].pk_length = ret.pk_length;
 primaryKeys[tableUsed].values = pks;
 tableUsed++;
 }
 SPI_freetuptable(SPI_tuptable);
 }
 }
 }
/** ---------------------------- END CHECK PKS IN Data BASE ---------------------------- **/
 if (ret.pk_length == 0)
 {
 elog(ERROR,"auditc.so: Erro find primary key");
 }
/** ---------------------------- GET PKS ---------------------------- **/
 int pk_pos;
 ret.pks = SPI_palloc(ret.pk_length * sizeof(string));
/** **************************** DIFF *****************************************/
 string column;
 string newValue;
 for(pos = colunsSize; pos > 0; pos--)
 {
 column = SPI_fname(description, pos);
 newValue = SPI_getvalue(newtuple, description, pos);
 for(pk_pos = 0; pk_pos < ret.pk_length; pk_pos++)
 {
 if (strcmp(column, pks[pk_pos]) == 0)
 {
 ret.pks[pk_pos] = newValue;
 }
 }
 if (oldtuple != 0)
 {
 string oldValue = SPI_getvalue(oldtuple, description, pos);
 if ((oldValue == 0 && newValue == 0)
 || (
 !(oldValue == 0 && newValue != 0)
 && !(oldValue != 0 && newValue == 0)
 && !strcmp(oldValue, newValue)))
 {
 continue;
 }
 }
 ret.fields[ret.size] = column;
 ret.data[ret.size] = newValue;
 ret.size++;
 }
/** ---------------------------- END DIFF ---------------------------- **/
 } else {
 ret.size = 0;
 }
#ifdef DEBUGAUDIT
 displaydiff(&ret);
#endif
/** **************************** SAVE IN LOG *****************************************/
 if (ret.size > 0 || operation == 'D')
 {
 Oid oids[6] = {VARCHAROID, VARCHAROID, CHAROID, OID_VARCHAR_ARRAY, TEXTARRAYOID, INT4OID};
 SPIPlanPtr prep = SPI_prepare("INSERT INTO log.audits"
 " (transaction, owner_role, owner_id, operation, fields, data)"
 " VALUES (txid_current(), $1, $2, $3, $4, $5)", 5, oids);
 if (prep)
 {
		//OWNER_ROLE COLUMN
 char *schema = SPI_getnspname(trigdata->tg_relation);
 char *table = SPI_getrelname(trigdata->tg_relation);
 char *st = (char *) SPI_palloc(strlen(schema) + strlen(table) + 2); /* +1 for null character */
 strcpy(st, schema);
 strcat(st, ".");
 strcat(st, table);
		//OWNER_ID COLUMN
 int size = strlen(ret.pks[0]);
 int pk_pos;
 for(pk_pos = 1; pk_pos < ret.pk_length; pk_pos++)
 {
 size += strlen(ret.pks[pk_pos]) + 1;
 }
 char *pkIds = (char *) SPI_palloc(size * sizeof(char) + 1); /* +1 for null character */
 strcpy(pkIds, ret.pks[0]);
 for(pk_pos = 1; pk_pos < ret.pk_length; pk_pos++)
 {
 char *val= ret.pks[pk_pos];
 strcat(pkIds, ",");
 strcat(pkIds, val);
 }
		//DATA COLUMN and FIELDS COLUMN
 Datum *fields = SPI_palloc(sizeof(Datum) * ret.size);
 Datum *values = SPI_palloc(sizeof(Datum) * ret.size);
 bool *valuesNulls = SPI_palloc(sizeof(bool) * ret.size);
 {
 int pk_pos;
 for(pk_pos = 0; pk_pos < ret.size; pk_pos++)
 {
 fields[pk_pos] = CStringGetDatum(cstring_to_text(ret.fields[pk_pos]));
 if (ret.data[pk_pos]) {
 values[pk_pos] = CStringGetDatum(cstring_to_text(ret.data[pk_pos]));
 valuesNulls[pk_pos] = false;
 } else {
 values[pk_pos] = 0;
 valuesNulls[pk_pos] = true;
 }
 }
 }
		//PARAMETERS For statement
 Datum *parameters = SPI_palloc(sizeof(Datum) * 6);
 parameters[0] = CStringGetDatum(cstring_to_text(st));
 parameters[1] = CStringGetDatum(cstring_to_text(pkIds));
 parameters[2] = CharGetDatum(operation);
 parameters[3] = (Datum) construct_array(fields, ret.size, VARCHAROID, -1, false, 'i');
 {
 int	* dims = SPI_palloc(sizeof(int));
 dims[0] = ret.size;
 int	* lbs = SPI_palloc(sizeof(int));
 lbs[0] = 1;
 parameters[4] = (Datum) construct_md_array(values, valuesNulls, 1, dims, lbs, VARCHAROID, -1, false, 'i');
 }
		//NULL VALUES
 char *nulls = SPI_palloc(sizeof(char) * 6);
 nulls[0] = ' ';
 nulls[1] = ' ';
 nulls[2] = ' ';
 nulls[3] = ret.size ? ' ' : 'n';
 nulls[4] = ret.size ? ' ' : 'n';
		//EXECUTE QUERY
 SPI_execute_plan(prep, parameters, nulls, false, 0);
		//CLEAN VARIABLES
 SPI_pfree(st);
 SPI_pfree(pkIds);
 SPI_pfree(parameters);
 SPI_pfree(nulls);
 } else {
 elog(ERROR,"auditc.so: Erro to create preparar: %d", SPI_result);
 }
 }
/** ---------------------------- END IN LOG ---------------------------- **/
	//CLEAN VARIABLES
 SPI_pfree(ret.pks);
 SPI_pfree(ret.data);
 SPI_pfree(ret.fields);
 if (SPI_finish() != SPI_OK_FINISH)
 {
 elog(ERROR,"auditc.so: Erro to close connection");
 }
 return PointerGetDatum(newtuple);
 } else {
 elog(ERROR,"auditc.so: Erro not get Connection");
 }
}
/**
 * Debug functions
 **/
#ifdef DEBUGAUDIT
display(TriggerData *trigdata)
{
 TupleDesc tupdesc = trigdata->tg_relation->rd_att;
 elog(INFO,"_________TRIGGER_INFO___________");
 elog(INFO,"nspname(schema) = %s", SPI_getnspname(trigdata->tg_relation));
 elog(INFO,"relname(table) = %s", SPI_getrelname(trigdata->tg_relation));
 elog(INFO,"_________TRIG_TUPLE_____________");
 displayvars(trigdata->tg_trigtuple, tupdesc);
 if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
 {
 elog(INFO,"_________TRIG_NEW_______________");
 displayvars(trigdata->tg_newtuple, tupdesc);
 }
}
displayvars(HeapTuple tupe, HeapTuple tupdesc)
{
 if (!tupe) return;
 uint32 coluns = HeapTupleHeaderGetNatts(tupe->t_data);
 elog(INFO,"colums size: %d",coluns);
 if (coluns > 0)
 {
 bool isnull = false;
 int pos;
 for(pos = coluns; pos > 0; pos--)
 {
 char* value = SPI_getvalue(tupe, tupdesc, pos);
 elog(INFO,"column %s value: %s",SPI_fname(tupdesc, pos), (value ? value : "(null)"));
 }
 }
}
displaydiff(diffret *ret)
{
 elog(INFO,"_________DIFF_VALUES____________");
 elog(INFO,"colums diff: %d",ret->size);
 if (ret->size < 1) return;
 int pos;
 for(pos = 0; pos < ret->size; pos++)
 {
 elog(INFO,"column %s diff: %s", ret->fields[pos], ret->data[pos]);
 }
}
displayconstraints(Relation relation)
{
 elog(INFO, "______CONSTRAINTS_OIDs_________");
 ListCell *i;
 foreach(i, relation->rd_indexlist)
 {
 Oid oid = lfirst(i);
 elog(INFO, "Oid: %d", oid);
 }
 elog(INFO, "______CONSTRAINTS_INDEX________");
 elog(INFO, "rd_index: %d", relation->rd_index);
 if (relation->rd_index)
 {
 elog(INFO, "indkey.ndim: %d", relation->rd_index->indkey.ndim);
 elog(INFO, "indkey.vl_len_: %d", relation->rd_index->indkey.vl_len_);
 }
}
#endif
=== Build commands ===
For compile:
cc -fpic -I/usr/include/pgsql/server/ -c audit.c
For create shared Lib: 
cc -shared -o audit.so audit.o
Move "audit.so" to postgres lib folder
=== Limitations ===
Required primarykey on table
=== GitHub ===
https://github.com/Piemontez/postgresqlauditc