PL/TCL trigger error "can't read "tgname": no such variable"

[indikamaligaspe]

Hi all,
I am trying to get a PL/TCL trigger to update an audit table working. The problem is when I create the trigger on an exiting table, I get the following error

"ERROR: can't read "tgname": no such variable
CONTEXT: can't read "tgname": no such variable
while executing ....."

If I drop the table and then recreate the table and add the rigger, the trigger works fine. However if I alter the table, again I have to drop and re-create the bale and triggers.

My problem is I have over 60 table to add the triggers to and each time I implement a change I can not have the tables to be dropped and recreated.

Following is sample of my tcl function

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 relfilenode = $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 this is my trigger

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"();

The problem is the error comes where I am trying to use the values queried from the catalogue $tgname, $schema_name etc...

Any help would be much apprciated

[indikamaligaspe]

Guys,
I found the solution to this, sorry was now able to post s reply till now.
The issue is in the following code

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 relfilenode = $TG_relid"

When you create a table the pg_class table gets a record for that table and the OID and relfilenode are the same in that record. When you create the trigger, the trigger gets mapped to the OID of the table in pg_class.

So in effect the $TG_relid is equal to both OID and relfilenode in the pg_class table.

However when you do modifications to the table (change column types / add constraints etc... the "relfilenode" value keeps on changing to match the physical data location. So when I run the query the $TG_relid no longer matches to "relfilenode".

So I changed the code to check the OID in pg_class rather then the "relfilenode" and that fixed the problem.