How to create a trigger for an entire table in postgresql?

Question

I have a trigger, but I need to link to all the tables of my postgres. Is there such a command below?

CREATE TRIGGER delete_data_alldb BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data(); 

Answer 1

Well, there is no database trigger creation, but for all such operations with admin arrays you could use PostgreSQL system tables to generate queries for you instead of writing them manually. In this case, you can run:

 SELECT 'CREATE TRIGGER ' || tab_name || ' BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();' AS trigger_creation_query FROM ( SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema NOT LIKE 'pg_toast%' ) tablist; 

This will give you a set of strings that are SQL commands, such as:

 CREATE TRIGGER schema1.table1 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data(); CREATE TRIGGER schema1.table2 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data(); CREATE TRIGGER schema1.table3 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data(); CREATE TRIGGER schema2.table1 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data(); CREATE TRIGGER schema2."TABLE2" BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data(); ... etc 

You just need to run them right away (either psql or pgAdmin).

Now a few explanations:

• I select table names in my database using the information_schema.tables system table. Since there is data from virtually all tables, be sure to exclude pg_catalog and information_schema schemas and toast tables from your select .
• I use the quote_ident(text) function, which, if necessary, will place the string inside the double quotation marks ( "" ) (that is, names with spaces or capital letters).
• When I have a list of table names, I just concatenate them with some static strings to get my SQL commands.
• I am writing this command using a subquery because I want you to better understand what is going on here. You can write one query by setting quote_ident(table_schema) || '.' || quote_ident(table_name) quote_ident(table_schema) || '.' || quote_ident(table_name) quote_ident(table_schema) || '.' || quote_ident(table_name) instead of tab_name .