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();

Erbes

No, there is no support for database-wide triggers. Why would you want to do this particualr thing anyway? – Picaroon

I need to record database actions to sync with the smartphone. Then I'll run this trigger table by table. Thanks for help. – Erbes

@EduardoRafaelCorreadeSouza I know that couple of days passed since you've asked that question and probably you done it by hand. But check out my answer. If it made you learn something useful which can help you in the future with similar task it would be nice of you to consider accepting my answer. – Bole

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 '
    || quote_ident(table_name || '_rowtgr')
    || ' BEFORE DELETE ON '
    || quote_ident(table_schema) || '.' || quote_ident(table_name)
    || ' FOR EACH ROW EXECUTE PROCEDURE delete_data();' AS trigger_creation_query
FROM information_schema.tables
WHERE
    table_schema NOT IN ('pg_catalog', 'information_schema')
        AND table_schema NOT LIKE 'pg_toast%';

This will get you set of strings which are SQL commands like:

CREATE TRIGGER table1_rowtgr BEFORE DELETE ON schema1.table1 FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER table2_rowtgr BEFORE DELETE ON schema1.table2 FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER table3_rowtgr BEFORE DELETE ON schema1.table3 FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER table1_rowtgr BEFORE DELETE ON schema2.table1 FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER "TABLE 2_rowtgr" BEFORE DELETE ON schema2."TABLE 2" FOR EACH ROW EXECUTE FUNCTION 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.
• Edited to ensure trigger name doesn't match target table name as per best practices (within a given schema).

see https://www.postgresql.org/docs/current/sql-createtrigger.html for proper syntax

Protectionism

I got subquery in FROM must have an alias, so added ) AS inner_select; to the end and it worked. – Florio

@IanVaughan thanks you're 100% right! I corrected my answer. – Bole

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
$$;

Charkha