Trigger with dynamic SQL

--0016e643594ad9c6d30486f19e2a
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e643594ad9c6d30486f19e2a
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

To avoid to delete registe=
rs I created one trigger activated "before delete" with lines lik=
e that:
"font-family: verdana,sans-serif;">UPDATE tableX=A0 set dtExc =3D 'now&=
#39;, userExc =3D current_user where idTableX =3D OLD.idTableX;
tyle=3D"font-family: verdana,sans-serif;">
return NULL;
le=3D"font-family: verdana,sans-serif;">
ans-serif;">but, I need do=
the same for many tables and I don't catch how.
t-family: verdana,sans-serif;">
I created an sql variable =
to construct the update command using parameters on trigger
=3D"font-family: verdana,sans-serif;"> ans-serif;">    =A0 qry :=3D 'UPDATE '||arg_table||' se=
t userexc =3D ' ||chr(39)||current_user||chr(39)||', dtalt =3D '=
;||'''now'''||' where ' || arg_id ||' =
=3D OLD.'||TG_ARGV[1];
if;">

verdana,sans-serif;">but when "EXECUTE qry" I lost the OLD.variab=
le.

family: verdana,sans-serif;">
I can't send the bigin=
t id to delete in trigger parameters.
na,sans-serif;">
=3D"font-family: verdana,sans-serif;">Any suggestions?
ont-family: verdana,sans-serif;">

verdana,sans-serif;">Thanks in advance,
dana,sans-serif;">Josi Per=
ez

Re: Trigger with dynamic SQL

--001636b14798a05c440487596837
Content-Type: text/plain; charset=ISO-8859-1

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)

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

--001636b14798a05c440487596837
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Sorry for the inconvenience, but no one have ideas to solve this problem? A=
m I in the wrong list to ask this?
Need I create triggers for each table=
?

Thanks in advance for any suggestions.
Josi Perez

Re: Trigger with dynamic SQL

--0016367fa760f720910487599457
Content-Type: text/plain; charset=UTF-8

2010/5/24 Josi Perez (3T Systems)

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

--0016367fa760f720910487599457
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Re: Trigger with dynamic SQL

On Wed, May 19, 2010 at 06:30, Josi Perez (3T Systems)
wrote:
> To avoid to delete registers I created one trigger activated "before dele=
te"
> with lines like that:
> UPDATE tableX  set dtExc =3D 'now', userExc =3D current_user where i=
dTableX =3D
> 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 :=3D 'UPDATE '||arg_table||' set usere=
xc =3D '
> ||chr(39)||current_user||chr(39)||', dtalt =3D '||'''now'''||' where ' ||
> arg_id ||' =3D 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 :=3D 'UPDATE '|| quote_ident(arg_table) ||
' SET userexc =3D current_user, dtalt =3D now() where '||
quote_literal(arg_id) ||' =3D '|| quote_literal(OLD.TG_ARGV[1]) ||';';

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Trigger with dynamic SQL

--0016e643594a35f57104875b8e81
Content-Type: text/plain; charset=ISO-8859-1

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

> 2010/5/24 Josi Perez (3T Systems)
>
> 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)
>>
>> 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
>

--0016e643594a35f57104875b8e81
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thank you.

The trigger:
CREATE OR REPLACE FUNCTION logdata()
=
=A0 RETURNS trigger AS
$BODY$DECLARE
   arg_table=A0 varchar;
=
   arg_id     varchar;
   arg_old  =A0 integer;
=
   qry      =A0 text;

BEGIN

arg_table :=3D TG_ARGV[0];=A0
arg_id  =A0 :=3D TG_ARGV[1];  =A0=
--field to use OLD.id
arg_old   :=3D TG_ARGV[2];   --value
<=
br>if TG_OP =3D 'INSERT' then
   new.userinc :=3D current_us=
er;
   new.dtinc :=3D 'now';

   return new;
elseif TG_OP =3D 'UPDATE' then
   new.=
useralt :=3D current_user;
   new.dtalt :=3D 'now';
=A0=
=A0 return new;

elseif TG_OP =3D 'DELETE' then
   =
---just user postgresW can delete

   if current_user <> 'postgresW' then
    =A0=
-- trying to mount the SQL
    =A0 --qry :=3D 'UPDATE '=
||arg_table||' set userexc =3D ' ||chr(39)||current_user||chr(39)||=
', dtalt =3D '||'''now'''||' where '=
; || arg_id ||' =3D OLD.'||TG_ARGV[1];

    =A0 qry :=3D 'UPDATE '||arg_table||' set userexc =
=3D ' ||chr(39)||current_user||chr(39)||', dtalt =3D '||'&#=
39;'now'''||' where ' || arg_id ||' =3D '||=
to_char(arg_old,'999999');


    =A0 --qry :=3D 'update '||quote_ident(arg_table)||&=
quot;set dtexc =3D now, userexc =3D current_user "||"where "=
||quote_ident(arg_id)||"=3DOLD."||quote_ident(arg_id)||";&qu=
ot;;

    =A0 raise notice 'QRY =3D %', qry;

    =A0 EXECUTE qry;
    =A0 --EXECUTE 'UPDATE '=
||arg_table||' set userexc =3D ' ||chr(39)||current_user||chr(39)||=
', dtalt =3D '||'''now'''||' where '=
; || arg_id ||' =3D OLD.'||TG_ARGV[1];

    =A0 --EXECUTE 'update '||quote_ident(arg_table)||' =
set userexc =3D'|| current_user ||' where '||quote_ident(arg_id=
)||' =3D OLD.'||quote_ident(arg_id)||';';
    =
=A0 --update opcao set dtexc =3D 'now', userexc =3D current_user >
    =A0 -- where idopcao =3D OLD.idopcao;
    =A0 return=
NULL;
   else
    =A0 return OLD;
   end if;<=
br>end if;
END;$BODY$
=A0 LANGUAGE 'plpgsql' VOLATILE
=A0 =
COST 100;
ALTER FUNCTION logdata() OWNER TO postgres;



To each table:
CREATE TRIGGER logdatatable
=A0 BEFORE INSERT =
OR UPDATE OR DELETE
=A0 ON opcao
=A0 FOR EACH ROW
=A0 EXECUTE PROC=
EDURE 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 ta=
ble.

Thanks in advance,
Josi Perez

Re: Trigger with dynamic SQL

--0016367b6c7274bb1304876fae43
Content-Type: text/plain; charset=ISO-8859-1

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)

> 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
>
> 2010/5/24 Josi Perez (3T Systems)
>>
>> 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)
>>>
>>> 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
>>
>
>

--0016367b6c7274bb1304876fae43
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Alex Hunsaker, thank you for your suggestion, but, the processor do not rep=
lace OLD.TG_ARGV[1] by the content.
If I use OLD in EXECUTE it is not re=
cognized.
I didn't get how to pass an integer variable to the trigge=
r.


I already create repeteadly times the trigger changing the UPDATE coman=
d inside the TG_OPER DELETE for each table, but, I really appreciate to kno=
w if there is a solution for this.

Thank you in advance,
Josi Per=
ez

Partitioned Tables

--_000_BD69807DAE0CE44CA00A8338D0FDD08302E4F39BFEoma00cexmbx 03_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

If I create a parent table that lives in a specific tablespace, when I crea=
te child tables that inherit the parent and then subsequently index and cre=
ate a PK on the child, will all of the index objects default to the tablesp=
ace that the parent is built in?


Regards,

Joe Plugge

--_000_BD69807DAE0CE44CA00A8338D0FDD08302E4F39BFEoma00cexmbx 03_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"
" xmlns=3D"http://www.w3.org/TR/REC-html40">


>