Skip site navigation (1) Skip section navigation (2)

Re: Trigger with dynamic SQL

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 (view raw or flat)
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]) ||';';

In response to

pgsql-admin by date

Next:From: Eduardo Sá dos ReisDate: 2010-05-24 18:49:11
Subject: Fw: Problems whit role and privileges
Previous:From: Szymon GuzDate: 2010-05-24 16:32:07
Subject: Re: Trigger with dynamic SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group