Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 661
adamek Avatar asked Aug 05 '13 02:08

adamek


People also ask

How do I find the table name in PostgreSQL?

To list the tables in the current database, you can run the \dt command, in psql : If you want to perform an SQL query instead, run this: SELECT table_name FROM information_schema.

Which variable contains the name of the table that caused the trigger invocation?

TG_RELID. Data type oid ; the object ID of the table that caused the trigger invocation.

How do I find a table name in Pgadmin?

Just right click on your schema then you will find a option search objects. Click on the option and select the option you want to search from the new opened window. Now fill the pattern as name or as per the option you have selected with prefix and suffix as "%". Save this answer.


2 Answers

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

like image 158
bma Avatar answered Sep 19 '22 14:09

bma


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!

like image 31
marcopeg Avatar answered Sep 18 '22 14:09

marcopeg