Re: Trigger with dynamic SQL - Mailing list pgsql-admin
| From | Josi Perez (3T Systems) |
|---|---|
| Subject | Re: Trigger with dynamic SQL |
| Date | |
| Msg-id | AANLkTimECRJEUXNC0bG8IwHDipUKtMFiCwMoYc3oMDXk@mail.gmail.com Whole thread Raw |
| In response to | Re: Trigger with dynamic SQL (Szymon Guz <mabewlun@gmail.com>) |
| Responses |
Re: Trigger with dynamic SQL
Partitioned Tables |
| List | pgsql-admin |
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
#avg_ls_inline_popup { position:absolute; z-index:9999; padding: 0px 0px; margin-left: 0px; margin-top: 0px; width: 240px; overflow: hidden; word-wrap: break-word; color: black; font-size: 10px; text-align: left; line-height: 13px;}
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@gmail.com>
2010/5/24 Josi Perez (3T Systems) <josiperez3t@gmail.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 Perez2010/5/19 Josi Perez (3T Systems) <josiperez3t@gmail.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 PerezWhat 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.regardsSzymon Guz
pgsql-admin by date: