如何在 PostgreSQL 触发器函数中获取表名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18049815/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get the table name in a PostgreSQL trigger function?
提问by adamek
I have a trigger function:
我有一个触发功能：
CREATE OR REPLACE FUNCTION "trigger_deleteUsers"()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO "DeletedEntities" ("uuidKey", "dateCreated", "dateModified", "dateSynced", "username", "entityName")
VALUES (OLD."uuidKey", OLD."dateCreated", OLD."dateModified", "dateSynced", OLD."username", 'Users');
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER "deleteUsers" AFTER DELETE ON "Users" FOR EACH ROW EXECUTE PROCEDURE "trigger_deleteUsers"();
This works for the table "Users". Every time I delete a row from the "Users" table the database inserts a row with ("uuidKey", "dateCreated", "dateModified", "dateSynced", "username", "entityName") into the table "DeletedEntities" that I will use for syncing purposes later.
这适用于“用户”表。每次我从“用户”表中删除一行时，数据库都会在表“DeletedEntities”中插入一行（“uuidKey”、“dateCreated”、“dateModified”、“dateSynced”、“username”、“entityName”）稍后我将用于同步目的。
The above works. Here's my problem I have about two dozen tables. I know I need to CREATE TRIGGER on each table, but I don't want to have to create a custom trigger function for each table. The only thing that would change from first function above is the last value in the INSERT statement within the function; instead of 'Users' it would be "Ledgers", or "Journal", or whatever.
以上工作。这是我的问题，我有大约两打桌子。我知道我需要在每个表上创建触发器，但我不想为每个表创建自定义触发器函数。与上面的第一个函数相比，唯一会改变的是函数内 INSERT 语句中的最后一个值；而不是“用户”，它会是“分类帐”或“日志”，或其他什么。
Within a PostgreSQL trigger function, how do I get the name of the table that the OLD row belongs too?
在 PostgreSQL 触发器函数中，如何获取 OLD 行所属的表的名称？
回答by bma
TG_TABLE_NAME. See the docs for other trigger arguments: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
TG_TABLE_NAME。有关其他触发器参数，请参阅文档：http: //www.postgresql.org/docs/current/static/plpgsql-trigger.html
回答by marcopeg
try this:
试试这个：
TG_TABLE_NAME::regclass::text
I use it with version 9.4 but it should be working from 8.4 up.
我在 9.4 版本中使用它，但它应该可以从 8.4 开始工作。
Here is your code with this change:
这是带有此更改的代码：
CREATE OR REPLACE FUNCTION "trigger_deleteUsers"()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO "DeletedEntities" ("uuidKey", "dateCreated", "dateModified", "dateSynced", "username", "entityName")
VALUES (OLD."uuidKey", OLD."dateCreated", OLD."dateModified", "dateSynced", OLD."username", TG_TABLE_NAME::regclass::text);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER "deleteUsers" AFTER DELETE ON "Users" FOR EACH ROW EXECUTE
PROCEDURE "trigger_deleteUsers"();
let me know if it helps!
如果有帮助，请告诉我！
