Обсуждение: capturing and storing query statement with rules
I would like to create a audit log of update and delete queries against a table. I want the log on the server side, since access will be done using various clients via ODBC. I would like to capture the user, current datetime and the query statement. My thought was to accomplish this by creating a rule that will insert the information into a log table. How can I captures the query statement so that I can place it in a insert query? Is there a variable in the server that holds the query statement as a string? Am I about to go in over my head (again)? Thanks, Andrew
Le Mardi 24 Juin 2003 20:17, Andrew Gould a écrit : > I would like to create a audit log of update and > delete queries against a table. I want the log on the > server side, since access will be done using various > clients via ODBC. I would like to capture the user, > current datetime and the query statement. My thought > was to accomplish this by creating a rule that will > insert the information into a log table. > To be server side, you need to use a trigger and a plpgsql function. On this page you will find more informations on trigger related functions: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql-trigger.html Perhaps you can compare OLD and NEW rows to know which rows will be updated. And you'll find at the end of the page how to get current user (current_user keyword) and current datetime (function now). > How can I captures the query statement so that I can > place it in a insert query? Is there a variable in > the server that holds the query statement as a string? > I don't think... or wasn't able to find something like this one in the documentation. Regards. -- Guillaume <!-- http://absfr.tuxfamily.org/ -->.
Guillaume LELARGE wrote: > Le Mardi 24 Juin 2003 20:17, Andrew Gould a écrit : >>I would like to create a audit log of update and >>delete queries against a table. I want the log on the >>server side, since access will be done using various >>clients via ODBC. I would like to capture the user, >>current datetime and the query statement. My thought >>was to accomplish this by creating a rule that will >>insert the information into a log table. > > To be server side, you need to use a trigger and a plpgsql function. On this > page you will find more informations on trigger related functions: > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql-trigger.html > Perhaps you can compare OLD and NEW rows to know which rows will be updated. > And you'll find at the end of the page how to get current user (current_user > keyword) and current datetime (function now). > >>How can I captures the query statement so that I can >>place it in a insert query? Is there a variable in >>the server that holds the query statement as a string? I think this shows how to do what you want: http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php HTH, Joe
Joe Conway wrote: > Guillaume LELARGE wrote: > >> Le Mardi 24 Juin 2003 20:17, Andrew Gould a écrit : >> >>> I would like to create a audit log of update and >>> delete queries against a table. I want the log on the >>> server side, since access will be done using various >>> clients via ODBC. I would like to capture the user, >>> current datetime and the query statement. My thought >>> was to accomplish this by creating a rule that will >>> insert the information into a log table. >> >> And you'll find at the end of the page how to get current user >> (current_user keyword) and current datetime (function now). >> >>> How can I captures the query statement so that I can >>> place it in a insert query? Is there a variable in >>> the server that holds the query statement as a string? > > I think this shows how to do what you want: > http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php Maybe debug_query_string should be mapped into a variable like CURRENT_USER? Perhaps something like CURRENT_QUERY? Mike Mascari mascarm@mascari.com
http://gborg.postgresql.org/project/tablelog/download/download.php or http://gborg.postgresql.org/project/audittrail/download/download.php?branch=devel I haven't used either, but table_log seems to be one that I may start using in the near future. On Tue, 2003-06-24 at 13:17, Andrew Gould wrote: > I would like to create a audit log of update and > delete queries against a table. I want the log on the > server side, since access will be done using various > clients via ODBC. I would like to capture the user, > current datetime and the query statement. My thought > was to accomplish this by creating a rule that will > insert the information into a log table. > > How can I captures the query statement so that I can > place it in a insert query? Is there a variable in > the server that holds the query statement as a string? > > Am I about to go in over my head (again)? > > Thanks, > > Andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Best Regards, Mike Benoit NetNation Communications Inc. Systems Engineer Tel: 604-684-6892 or 888-983-6600 --------------------------------------- Disclaimer: Opinions expressed here are my own and not necessarily those of my employer