Thread: Dynamic fieldname with NEW structure in trigger function?

Dynamic fieldname with NEW structure in trigger function?

From
Erwin Brandstetter
Date:
Hi List!

I have discussed the following problem with pl/pgsql on irc://freenode/postgresql. <irc://freenode/postgresql>

Christopher Kings-Lynne has asked me to post the problem here as a feature request, so here goes:
I think it is best formulated in plpgsql code below.

Kris Jurka has mentioned that something like this might work with pl/tcl and pl/python.
I am not experienced with those however, so I can't comment on that



CREATE FUNCTION trg_log_up_other() RETURNS trigger  AS '
DECLARE  sql_txt text;  up_table text;  up_field text;
BEGIN  up_table := TG_ARGV[0];  up_field := TG_ARGV[1];  sql_txt := ''UPDATE ''  || quote_ident(up_table)  || '' SET
somefield= somevalue''  || '' WHERE ''  ||  quote_ident(up_field)  || '' =  ''  || NEW.staticname_id;
 
-- ^^^^^^^^^^^^^^^^^^^^^ !!! last line = EVIL HACK !!!
-- I want a dynamic name passed by TG_ARGV[2] instead of staticname_id.
-- But NEW is not known inside EXECUTE, so it throws an error and does not evaluate.
-- But how can i evaluate a dynamic field if not with EXECUTE?
-- How do i get to the value of the field?  EXECUTE sql_txt;  return NEW;
END;
'  LANGUAGE plpgsql;



Thanx for considering.
Please cc me on replies.


Regards
Erwin Brandstetter