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

问题描述

我正在尝试构建一个审计触发函数(适用于多个表)，该函数可以检索表主键的列.例如，有一个以"customerid"作为主键的表客户，有一个以"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屋！