使用PostgreSQL触发器动态审核数据 [英] Dynamic auditing of data with PostgreSQL trigger

问题描述

我有兴趣在现有的PostgreSQL数据库中使用以下审核机制.

I'm interested in using the following audit mechanism in an existing PostgreSQL database.

http://wiki.postgresql.org/wiki/Audit_trigger

但是，希望(如果可能)进行一种修改.我还想将primary_key的值记录在以后可以查询的位置.因此，我想在"logged_actions"表中添加一个名为"record_id"的字段.问题在于现有数据库中的每个表都有一个不同的主键字段名.好消息是数据库具有非常一致的命名约定.一直是_id.因此，如果表被命名为"employee"，则主键为"employee_id".

but, would like (if possible) to make one modification. I would also like to log the primary_key's value where it could be queried later. So, I would like to add a field named something like "record_id" to the "logged_actions" table. The problem is that every table in the existing database has a different primary key fieldname. The good news is that the database has a very consistent naming convention. It's always, _id. So, if a table was named "employee", the primary key is "employee_id".

反正有这样做吗?基本上，我需要OLD.FieldByName(x)或OLD [x]之类的东西才能从id字段中获取值，然后将其放入新审核记录的record_id字段中.

Is there anyway to do this? basically, I need something like OLD.FieldByName(x) or OLD[x] to get value out of the id field to put into the record_id field in the new audit record.

我确实知道，我可以为要跟踪的每个表创建一个单独的自定义触发器，但是最好将其通用.

I do understand that I could just create a separate, custom trigger for each table that I want to keep track of, but it would be nice to have it be generic.

edit:我也了解该键值确实记录在旧/新数据字段中.但是，我想要的是使查询历史记录更加容易和高效.换句话说，

edit: I also understand that the key value does get logged in either the old/new data fields. But, what I would like would be to make querying for the history easier and more efficient. In other words,

select * from audit.logged_actions where table_name = 'xxxx' and record_id = 12345;

另一个我正在使用PostgreSQL 9.1

another edit: I'm using PostgreSQL 9.1

谢谢！

推荐答案

您没有提到PostgreSQL版本，这对于编写此类问题的答案非常重要.

You didn't mention your version of PostgreSQL, which is very important when writing answers to questions like this.

如果您运行的是PostgreSQL 9.0或更高版本(或能够升级)，则可以使用Pavel记录的这种方法:

If you're running PostgreSQL 9.0 or newer (or able to upgrade) you can use this approach as documented by Pavel:

http://okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html

通常，您想要的是在记录类型的PL/PgSQL变量(如"NEW"或"OLD")中引用动态命名的字段.从历史上看，这一直很烦人，但仍然很尴尬，但至少在9.0中是可能的.

In general, what you want is to reference a dynamically named field in a record-typed PL/PgSQL variable like 'NEW' or 'OLD'. This has historically been annoyingly hard, and is still awkward but is at least possible in 9.0.

您的另一种选择-可能更简单-是在动态字段引用很简单的plperlu中编写审核触发器.

Your other alternative - which may be simpler - is to write your audit triggers in plperlu, where dynamic field references are trivial.

这篇关于使用PostgreSQL触发器动态审核数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！