如何在 Postgresql 中获取结合 old.variablename 的列数据? [英] How to get column data combining old.variablename in Postgresql?

问题描述

我正在尝试构建一个审计触发器函数(适用于多个表)来检索表的主键列.例如，有一个表customers 的主键是'customerid'，还有一个表orders 的主键是'orderid'.

I'm trying to build an audit trigger function (that works for more than one table) that retrieves the column of the table's primary key. For example, there's a table customers that has 'customerid' as primary key, and there's a table orders that has 'orderid' as primary key.

通过这个，我找到了触发函数的表的列/主键的名称并将其存储在prim"中.

With this, i find the name of the column/primary key of the table that triggered the function and store it in 'prim'.

prim :=(SELECT c.column_name
    FROM information_schema.key_column_usage AS c
    LEFT JOIN information_schema.table_constraints AS t
    ON t.constraint_name = c.constraint_name
    WHERE(t.table_name = TG_TABLE_NAME) AND t.constraint_type = 'PRIMARY 
KEY');

这确实有效.Prim 的字面意思是orderid"或customerid"，具体取决于哪个表触发了该函数.现在，我想要做的是获取我检索到的列的信息，并将其插入到审计表中.因此，假设我从客户表中删除了一行，我想将该事件插入到审计表中.我尝试这样做:

That actually works. Prim would be literally 'orderid' or 'customerid', depending on which table triggered the function. Now, what i want to do is get the info of the column i retrieved, and insert it in the audit table. So let's say I deleted a row from the customers table, i want to insert that event in the audit table. I try to do:

INSERT INTO audit(columnname,dataincolumn,tablename)
VALUES(prim,old.prim,tg_table_name);

我知道它有很多问题，但我怎样才能得到我的输出:

I know there's a lot wrong with it, but how can I get my output to be:

COLUMN     DATAINCOLUMN    TABLE
--------------------------------
customersid    12     Customers
orderid        23     Orders

我收到以下错误:

ERROR:  record "old" has no field "prim"

推荐答案

这有点棘手，但您可以使用动态 SQL 并将值转换为正确的类型.

This is a bit tricky, but you can do it using dynamic SQL and casting the value to the correct type.

假设您有一个 bigint 变量 val(或 integer，取决于您的需要)，您可以:

Assuming that you have a bigint variable val (or integer, depending on your needs), you can do:

EXECUTE format('SELECT (($1)::%I).%I', TG_TABLE_NAME, prim)
INTO val
USING OLD;

这篇关于如何在 Postgresql 中获取结合 old.variablename 的列数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！