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

Re: Trigger with dynamic SQL

From: "Josi Perez (3T Systems)" <josiperez3t(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Trigger with dynamic SQL
Date: 2010-05-25 18:54:04
Message-ID: AANLkTilIYqkMMZ0OmFw9j0hcrmcbJvps6IeZPKzwFn2t@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Alex Hunsaker, thank you for your suggestion, but, the processor do not
replace OLD.TG_ARGV[1] by the content.
If I use OLD in EXECUTE it is not recognized.
I didn't get how to pass an integer variable to the trigger.

I already create repeteadly times the trigger changing the UPDATE comand
inside the TG_OPER DELETE for each table, but, I really appreciate to know
if there is a solution for this.

Thank you in advance,
Josi Perez


2010/5/24 Josi Perez (3T Systems) <josiperez3t(at)gmail(dot)com>

> Thank you.
>
> The trigger:
> CREATE OR REPLACE FUNCTION logdata()
>   RETURNS trigger AS
> $BODY$DECLARE
>    arg_table  varchar;
>    arg_id     varchar;
>    arg_old    integer;
>    qry        text;
>
> BEGIN
> arg_table := TG_ARGV[0];
> arg_id    := TG_ARGV[1];    --field to use OLD.id
> arg_old   := TG_ARGV[2];   --value
>
> if TG_OP = 'INSERT' then
>    new.userinc := current_user;
>    new.dtinc := 'now';
>    return new;
> elseif TG_OP = 'UPDATE' then
>    new.useralt := current_user;
>    new.dtalt := 'now';
>    return new;
>
> *elseif TG_OP = 'DELETE' then
>    ---just user postgresW can delete
>    if current_user <> 'postgresW' then
>       -- trying to mount the SQL
>       --qry := 'UPDATE '||arg_table||' set userexc = '
> ||chr(39)||current_user||chr(39)||', dtalt = '||'''now'''||' where ' ||
> arg_id ||' = OLD.'||TG_ARGV[1];
>       qry := 'UPDATE '||arg_table||' set userexc = '
> ||chr(39)||current_user||chr(39)||', dtalt = '||'''now'''||' where ' ||
> arg_id ||' = '||to_char(arg_old,'999999');
>
>       --qry := 'update '||quote_ident(arg_table)||"set dtexc = now, userexc
> = current_user "||"where
> "||quote_ident(arg_id)||"=OLD."||quote_ident(arg_id)||";";
>
>       raise notice 'QRY = %', qry;
>       EXECUTE qry;
>       --EXECUTE 'UPDATE '||arg_table||' set userexc = '
> ||chr(39)||current_user||chr(39)||', dtalt = '||'''now'''||' where ' ||
> arg_id ||' = OLD.'||TG_ARGV[1];
>       --EXECUTE 'update '||quote_ident(arg_table)||' set userexc ='||
> current_user ||' where '||quote_ident(arg_id)||' =
> OLD.'||quote_ident(arg_id)||';';
>       --update opcao set dtexc = 'now', userexc = current_user
>       -- where idopcao = OLD.idopcao;
>       return NULL;*
>    else
>       return OLD;
>    end if;
> end if;
> END;$BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>   COST 100;
> ALTER FUNCTION logdata() OWNER TO postgres;
>
>
> To each table:
> CREATE TRIGGER logdatatable
>   BEFORE INSERT OR UPDATE OR DELETE
>   ON opcao
>   FOR EACH ROW
>   EXECUTE PROCEDURE logdata('op', 'idop', idop);
>
> I could not send the current idop (integer) to mount a SQL without OLD.
> The goal is to use the same trigger changing just the parameters in each
> table.
>
> Thanks in advance,
> Josi Perez
>
>
>
> 2010/5/24 Szymon Guz <mabewlun(at)gmail(dot)com>
>
> 2010/5/24 Josi Perez (3T Systems) <josiperez3t(at)gmail(dot)com>
>>
>> Sorry for the inconvenience, but no one have ideas to solve this problem?
>>> Am I in the wrong list to ask this?
>>> Need I create triggers for each table?
>>>
>>> Thanks in advance for any suggestions.
>>> Josi Perez
>>>
>>> 2010/5/19 Josi Perez (3T Systems) <josiperez3t(at)gmail(dot)com>
>>>
>>> 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.
>>>>
>>>> I can't send the bigint id to delete in trigger parameters.
>>>>
>>>> Any suggestions?
>>>>
>>>> Thanks in advance,
>>>> Josi Perez
>>>>
>>>>
>>>
>> What is the problem? What do you mean by "lost the OLD.variable"? Better
>> show us the whole trigger code as I really don't get it.
>>
>> regards
>> Szymon Guz
>>
>
>

In response to

pgsql-admin by date

Next:From: Alvaro HerreraDate: 2010-05-25 18:59:37
Subject: Re: How can I tell if I'm autovacuuming?
Previous:From: Mitch CollinsworthDate: 2010-05-25 18:40:04
Subject: Re: How can I tell if I'm autovacuuming?

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