Re: Trigger with dynamic SQL

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
>>
>
>