By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,083 Members | 1,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,083 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Jul 6 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
1 Reply


P: 2
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.
Jul 18 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.