How can I get the table name in a PostgreSQL trigger function?

Question

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.

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.

Within a PostgreSQL trigger function, how do I get the name of the table that the OLD row belongs too?

bma · Accepted Answer · 2013-08-05 03:32:22Z

50

TG_TABLE_NAME. See the docs for other trigger arguments: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

edited Aug 5, 2013 at 3:32

answered Aug 5, 2013 at 2:36

bma

9,8362 gold badges35 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marcopeg · Accepted Answer · 2018-07-14 12:26:36Z

10

try this:

TG_TABLE_NAME::regclass::text

I use it with version 9.4 but it should be working from 8.4 up.

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!

answered Jul 14, 2018 at 12:26

marcopeg

1,25813 silver badges17 bronze badges

1 Comment

Ben Asmussen Over a year ago

Works on a Postgresql 12.2.

Collectives™ on Stack Overflow

How can I get the table name in a PostgreSQL trigger function?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related