take a look at dblink in the contrib directory, it has a function called dblink_current_query() that returns the current query. I use it all the time. Jim ---------- Original Message ----------- From: "rlee0001" <robeddielee@xxxxxxxxxxx> To: pgsql-general@xxxxxxxxxxxxxx Sent: 11 Jan 2006 14:57:42 -0800 Subject: [GENERAL] Returning SQL statement > I want to write a row-level trigger in PL/PGSQL that inserts rows into > an audit log whenever records are UPDATEd for a specific table. In my > audit log I want to record: > > * The primary key of the record being modified (easy) > * The current date (easy) > * The username of the user (easy) > * The SQL statement used to modify the record > > The last one is the most important and it seems to be impossible. Lets > assume the user executes the following statement: > > UPDATE inventory SET > status=5, > location_detail='somewhere over the rainbow', > policy=1, > security_comments='', > excludes_sms=false, > excludes_epo=false, > excludes_ws=false > WHERE asset_serial='jg432lk'; > > ...I want a field in the audit log to actually contain the above > statement text. The only way I can imagine to do it would be to have a > field in "inventory" called "AUDIT_SQL" and have my PHP application > populate that field with the statement (sans the "AUDIT_SQL='...'," > line itself). I can then have my trigger NULL-out this field in the NEW > row. The problem is that several users (including me) use EMS > PostgreSQL Manager to update data as well and I want these manual data > entries to be audited as well. If I don't update the "AUDIT_SQL" > command by hand it just won't get done and NULL will be seen in > "AUDIT_SQL" by any subsequent invokations of the trigger from within > EMS. Or worse user who see this field can falsify it. To keep users > from falisifying the field I could MD5-encode its contents and store > the hash in a seperate "AUDIT_HASH" field and have the trigger perform > an integrity check, canceling the operation if the hash is incorrect. > But the savy (and malicious) user can bypass this check and still > falsify the audit data. Is there a better way to pass such dynamic data > into a trigger procedure? Perhaps there is a way to store data in > "variables"? Something like this: > > SET mysqlvariable = 'hello world'; > UPDATE ...; > > ...Then the UPDATE trigger can read my parameters from the variable. > This might be ideal. > > So in the meantime, since I can't figure out how to do the SQL thing, I > have my PHP record user activity into the audit log and activity done > from within EMS is not being recorded at all. This is not desirable. > > So my question is this: Is there a PostgreSQL function that will return > the statement that triggered the trigger without the end user/PHP > having to provide it? Perhaps a function that will return the last > statement that was executed? Can you even calculate a MD5 hash in > PL/PGSQL (I assume so)? > > I'm using PG 8.1. I have access to both PL/PGSQL and (maybe) PL/Perl. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org ------- End of Original Message -------
