Hence I started digging in to the issue and following is some insight into the way that we can create a PL / TCL trigger.
Why PL/TCL
Well in PsotgreSQL, there are many languages to create triggers from. PL/SQL, PL/TCL. PL/Python and PL/Perl. Of the four PL/SQL is the most famous for procedural program blocks in SQL DBMS. However there is a limitation in PL/SQL in that we can not have dynamic table names or columns names to be used within the trigger.
i.e. if we are to use the column that was changed and the table that was changed dynamically we would have an issue, because in PL/SQL we will not be able to pick the columns which are changed dynamically and they need to be statically coded in the PL/SQL function. This would cause us to have a separate PL/SQL function for each and every table we are going to add the trigger to or create separate trigger functions.
Hence PL/TCL to the rescue. With PL/TCL we can use C functions in PostgreSQL to get an array of changed columns and tables. Of course we would need to combine PL/TCL with PostgreSQL catalogue as well.
Set up
First we need to add PL/TCL capability to the database. In order to do this we need to compile PostgreSQL installation with adding PL/TCL. Once that is done we need to enable PL/TCL to the database.i.e. using the CREATELANG function
createlang pltcl dbname or createlang pltclu dbname
Code
Following is an expert from a code that would create trigger function, and how that function can be attached to a table.CREATE OR REPLACE FUNCTION xxx.log_to_audit_table()
RETURNS "trigger" AS
$BODY$
spi_exec "SELECT CURRENT_USER AS tguser"
spi_exec "SELECT c.relname AS tgname,n.nspname AS schema_name
FROM pg_class c , pg_namespace n
WHERE n.oid = c.relnamespace
AND c.oid = $TG_relid"
spi_exec "insert into xxx.testlogtable values ('inserted in log_to_audit_table $tgname')";
set pk_name ""
spi_exec "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index i
WHERE c.relname = '$tgname'
AND c.oid=i.indrelid
AND a.attnum > 0
AND a.attrelid = i.indexrelid
AND i.indisprimary='t'"
spi_exec "SELECT audit_table_name AS m_aud_tbl_name
FROM $main_schema.audit_table_mapping
where schema_name = '$schema_name'
and tabel_name = '$tgname'"
.....
return OK
$BODY$
LANGUAGE 'pltcl' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;And following is the trigger created to a table to audit
CREATE TRIGGER trig_audit_admin_user
AFTER INSERT OR UPDATE OR DELETE
ON xxx."user"
FOR EACH ROW
EXECUTE PROCEDURE "xxx"."log_to_audit_table"();
Well that's it. Whenever you need to add audit feature to a table, add the trigger to the table.
