How to create trigger for all table in postgresql?

Question

I have a trigger, but I need to associate with all tables of the my postgres. Is there a command like this 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-wide trigger creation but for all such bulk-admin-operations you could use PostgreSQL system tables to generate queries for you instead of writing them by hand. In this case you could 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 get you set of strings which are SQL commands like:

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 at once (either by psql or pgAdmin).

Now some explanation:

• I select names of tables in my database using information_schema.tables system table. Because there are data of literally all tables, remember to exclude pg_catalog and information_schema schemas and toast tables from your select.
• I use quote_ident(text) function which will put string inside double quote signs ("") if necessary (ie. names with spaces or capital letters require that).
• When I have list of tables names I just concatenate them with some static strings to get my SQL commands.
• I write that command using sub-query because I want you to get better idea of what's going on here. You may write a single query by putting quote_ident(table_schema) || '.' || quote_ident(table_name) in place of tab_name.

Answer 2

A conveniently encapsulated version of Gabriel's answer. This time I am using the trigger to update a column named update_dt datetime granted to be part of any table in the public schema of the current database.

--
-- function:  tg_any_update_datetime_fn
-- when:      before insert or update
--
create or replace function tg_any_update_datetime_fn () 
returns trigger 
language plpgsql as $$
begin
  new.update_dt = now();
  return new;
end;
$$;

--
-- function:  ddl_create_before_update_trigger_on_all_tables
-- returns:   Create a before update trigger on all tables.
--
create or replace procedure ddl_create_before_update_trigger_on_all_tables ()
language plpgsql as $$
declare
  _sql varchar;
begin
  for _sql in select concat (
      'create trigger tg_',
      quote_ident(table_name),
      '_before_update before update on ',
      quote_ident(table_name),
      ' for each row execute procedure tg_any_update_datetime_fn ();'
    )
    from
      information_schema.tables
    where  
      table_schema not in ('pg_catalog', 'information_schema') and    
      table_schema not like 'pg_toast%'
  loop
    execute _sql;
  end loop;
end;
$$;

-- create before update trigger on all tables
call ddl_create_before_update_trigger_on_all_tables();

On my DDL scripts I use a large number of such ddl_ functions that have only meaning at DDL time. To remove them from the database use

--
-- function:  ddl_drop_ddl_functions
-- returns:   Drop all DDL functions.
-- since:     1.1.20
--
create or replace procedure ddl_drop_ddl_functions ()
language plpgsql as $$
declare
  r record;
  _sql varchar;
begin
  for r in
    select oid, prokind, proname
    from   pg_proc
    where  pronamespace = 'public'::regnamespace
    and    proname ilike 'ddl_%'
  loop
    case r.prokind
      when 'a' then _sql = 'aggregate';
      when 'p' then _sql = 'procedure';
      else          _sql = 'function';
    end case;

    _sql = format('drop %s %s', _sql, r.oid::regprocedure);

    execute _sql;
  end loop;
end
$$;