Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger to create string to inverse SQL statement

Trigger to create string to inverse SQL statement

From
Łukasz Jarych
Date:
Hello,

i have a trigger which is added log history:



It is possible to create additional column here with string with inversed SQL statement? 

So in this case: "Delete FROM t_trig WHERE ID=1". 

And what i want is to have possibility to loop through table and execute inversed sql statement for each row. 

Please help,
Jacek Antek


Attachment

Re: Trigger to create string to inverse SQL statement

From
Francisco Olarte
Date:
Hello:

On Thu, Mar 8, 2018 at 1:32 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:


i have a trigger which is added log history:




​I'll trust it is, but I cannot really see any thing in there, it may be better if in the future you used something like psql and pasted the data in a text format, which people with no-so-good eyesight like me can zoom in.​

 
It is possible to create additional column here with string with inversed SQL statement? 

So in this case: "Delete FROM t_trig WHERE ID=1". 

And what i want is to have possibility to loop through table and execute inversed sql statement for each row. 

​It should be. For inserts it is not that difficult, for updates YMMV, it is difficult to ​reverse a command exactly, but you get new and old values so you could write it. For deletes just reinsert. But I doubt there is a function which does it for you, you'll have to code something, and I suspect it may be easier to just capture op, old and new in your triggers and use an external program to build the inverted queries.

​Francisco Olarte.​

Attachment

Re: Trigger to create string to inverse SQL statement

From
Łukasz Jarych
Date:
thank you,

what do you mean use external program to build inverted queries. Do you have any examples?

Best,
Jacek  

2018-03-08 13:44 GMT+01:00 Francisco Olarte <folarte@peoplecall.com>:
Hello:

On Thu, Mar 8, 2018 at 1:32 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:


i have a trigger which is added log history:




​I'll trust it is, but I cannot really see any thing in there, it may be better if in the future you used something like psql and pasted the data in a text format, which people with no-so-good eyesight like me can zoom in.​

 
It is possible to create additional column here with string with inversed SQL statement? 

So in this case: "Delete FROM t_trig WHERE ID=1". 

And what i want is to have possibility to loop through table and execute inversed sql statement for each row. 

​It should be. For inserts it is not that difficult, for updates YMMV, it is difficult to ​reverse a command exactly, but you get new and old values so you could write it. For deletes just reinsert. But I doubt there is a function which does it for you, you'll have to code something, and I suspect it may be easier to just capture op, old and new in your triggers and use an external program to build the inverted queries.

​Francisco Olarte.​


Attachment

Re: Trigger to create string to inverse SQL statement

From
Łukasz Jarych
Date:
maybe somebody wrote SP to invert update statement?

Best,
Jacek 

2018-03-08 13:51 GMT+01:00 Łukasz Jarych <jaryszek@gmail.com>:
thank you,

what do you mean use external program to build inverted queries. Do you have any examples?

Best,
Jacek  

2018-03-08 13:44 GMT+01:00 Francisco Olarte <folarte@peoplecall.com>:
Hello:

On Thu, Mar 8, 2018 at 1:32 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:


i have a trigger which is added log history:




​I'll trust it is, but I cannot really see any thing in there, it may be better if in the future you used something like psql and pasted the data in a text format, which people with no-so-good eyesight like me can zoom in.​

 
It is possible to create additional column here with string with inversed SQL statement? 

So in this case: "Delete FROM t_trig WHERE ID=1". 

And what i want is to have possibility to loop through table and execute inversed sql statement for each row. 

​It should be. For inserts it is not that difficult, for updates YMMV, it is difficult to ​reverse a command exactly, but you get new and old values so you could write it. For deletes just reinsert. But I doubt there is a function which does it for you, you'll have to code something, and I suspect it may be easier to just capture op, old and new in your triggers and use an external program to build the inverted queries.

​Francisco Olarte.​



Attachment

Re: Trigger to create string to inverse SQL statement

From
Francisco Olarte
Date:
Hello:

On Thu, Mar 8, 2018 at 1:51 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:
> what do you mean use external program to build inverted queries. Do you have any examples?

Please, do not top quote, or the thread will get difficult to follow fast.

That being said. Capturing a DML in a trigger is relatively easy. On
recent postgres I've seen just using json to capture the whole new and
old rows, which looks promissing.

But then, building a 'rollback' DML is difficult to do in sql or
pl-pgsql, you would normally want a more normal programming language.
i.e., I would normally turn to perl for this, having used it since the
mid 90s.

Then, you have pl-perl, but this is difficult to debug/manage. So what
I would normally do is to just capture the changes in a trigger and
then have a program which queries the log table, builds the anti-query
and executes it ( no point in keeping it, since once you execute it
there is nothing to undo ). The beauty of this is you can take a
sample from your log table and easily test the program just
implementing a debug flag which prints the queries instead of
executing ( and does not touch the log table, which I assume a real
undoer will need to fro record-keepint ).

This is assuming the log is used as an "undo log", which is what I
would assume from the very scarce information I have. And this kind of
programs normally are seldom used, so postponing the query building to
a later time and logging minimal info fast is normally better ( is
like backups, you normally make backups faster, then optimize what you
can of restores, as ideally they would never be used, or transactions,
you normally optimize for commits first, then rollbacks ).

Francisco Olarte.

Re: Trigger to create string to inverse SQL statement

From
Francisco Olarte
Date:
Jacek:

On Thu, Mar 8, 2018 at 1:53 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:
> maybe somebody wrote SP to invert update statement?

Maybe, but if you are going to ask for it you better define the
problem a little.

Francisco Olarte.