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_audi t_table()
RETURNS "trigger" AS
$BODY$


spi_exec "SELECT CURRENT_USER AS tguser"


spi_exec "SELECT c.relname AS tgname,n.nspnam e AS schema_name
FROM pg_class c , pg_namespace n
WHERE n.oid = c.relnamespace
AND relfilenode = $TG_relid"



spi_exec "insert into xxx.testlogtabl e values ('inserted in log_to_audit_ta ble $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.indreli d
AND a.attnum > 0
AND a.attrelid = i.indexrelid
AND i.indisprimary= 't'"

spi_exec "SELECT audit_table_nam e AS m_aud_tbl_name
FROM $main_schema.au dit_table_mappi ng
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_admi n_user
AFTER INSERT OR UPDATE OR DELETE
ON xxx."user"
FOR EACH ROW
EXECUTE PROCEDURE "xxx"."log_to_a udit_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]

PL/TCL trigger error "can't read "tgname&qu ot;: no such variable" Reply to Thread

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.nspnam e 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 "relfilenod e" value keeps on changing to match the physical data location. So when I run the query the $TG_relid no longer matches to "relfilenod e".

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