From: | Alex Hunsaker <badalex(at)gmail(dot)com> |
---|---|
To: | "Josi Perez (3T Systems)" <josiperez3t(at)gmail(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Trigger with dynamic SQL |
Date: | 2010-05-24 18:14:13 |
Message-ID: | AANLkTimEmuku6afAV1S_0Qp0uZAcdg5BwRTlYRPaccJO@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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]) ||';';
From | Date | Subject | |
---|---|---|---|
Next Message | Eduardo Sá dos Reis | 2010-05-24 18:49:11 | Fw: Problems whit role and privileges |
Previous Message | Szymon Guz | 2010-05-24 16:32:07 | Re: Trigger with dynamic SQL |