Re: Trigger with dynamic SQL

On Wed, May 19, 2010 at 06:30, Josi Perez (3T Systems)
<josiperez3t(at)gmail(dot)com> wrote:
> To avoid to delete registers I created one trigger activated "before delete"
> with lines like that:
> UPDATE tableX  set dtExc = 'now', userExc = current_user where idTableX =
> OLD.idTableX;
> return NULL;
>
> but, I need do the same for many tables and I don't catch how.
> I created an sql variable to construct the update command using parameters
> on trigger
>       qry := 'UPDATE '||arg_table||' set userexc = '
> ||chr(39)||current_user||chr(39)||', dtalt = '||'''now'''||' where ' ||
> arg_id ||' = OLD.'||TG_ARGV[1];
>
> but when "EXECUTE qry" I lost the OLD.variable.

Yeah, when the update runs its not running under pgpsql so referencing
OLD wont work. Try something like (note completely untested):

qry := 'UPDATE '|| quote_ident(arg_table) ||
' SET userexc = current_user, dtalt = now() where '||
quote_literal(arg_id) ||' = '|| quote_literal(OLD.TG_ARGV[1]) ||';';