使用PostgreSQL触发器动态审计数据
我有兴趣在现有 PostgreSQL 数据库中使用以下审核机制。
http://wiki.postgresql.org/wiki/Audit_trigger
但是，愿意（如果可能的话）进行一项修改。我还想记录 Primary_key 的值，以便稍后查询。因此，我想将一个名为“record_id”的字段添加到“logged_actions”表中。问题是现有数据库中的每个表都有不同的主键字段名。好消息是数据库具有非常一致的命名约定。总是，_id。因此，如果表名为“employee”，则主键为“employee_id”。
有办法做到这一点吗？基本上，我需要类似 OLD.FieldByName(x) 或 OLD[x] 的内容来从 id 字段中获取值，并将其放入新审计记录中的 record_id 字段中。
我确实知道我可以为我想要跟踪的每个表创建一个单独的自定义触发器，但如果它是通用的那就太好了。
编辑：我还了解到键值确实会记录在旧/新数据字段中。但是，我想要的是让历史查询变得更容易、更高效。换句话说，
select * from audit.logged_actions where table_name = 'xxxx' and record_id = 12345;
另一个编辑：我正在使用 PostgreSQL 9.1
谢谢！
I'm interested in using the following audit mechanism in an existing PostgreSQL database.
http://wiki.postgresql.org/wiki/Audit_trigger
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".
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: 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;
another edit: I'm using PostgreSQL 9.1
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您没有提到您的 PostgreSQL 版本，这在编写此类问题的答案时非常重要。
如果您运行的是 PostgreSQL 9.0 或更高版本（或能够升级），您可以使用 Pavel 记录的这种方法：
http://okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html
在一般来说，您想要的是引用记录类型 PL/PgSQL 变量中的动态命名字段，例如“NEW”或“OLD”。这在历史上一直是非常困难的，而且仍然很尴尬，但至少在 9.0 中是可能的。
您的另一个选择（可能更简单）是在 plperlu 中编写审核触发器，其中动态字段引用很简单。
You didn't mention your version of PostgreSQL, which is very important when writing answers to questions like this.
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
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.
Your other alternative - which may be simpler - is to write your audit triggers in plperlu, where dynamic field references are trivial.