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?
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.
TG_RELID. Data type oid ; the object ID of the table that caused the trigger invocation.
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.
TG_TABLE_NAME. See the docs for other trigger arguments: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With