0

How can we capture modified dates for all the tables in PGSQL (apart from writing data triggers on each table for inserts /deletes).Can we do it at a generic level for all the tables using event triggers. for eg: the below query captures modified dates for every function in a generic .

 `select now(), nspname, proname, command_tag, prosrc
    from pg_event_trigger_ddl_commands() e
    join pg_proc p on p.oid = e.objid
    join pg_namespace n on n.oid = pronamespace;`

Likewise ,is there any to capture last modified date logs for all tables ?

Have Tried ,

CREATE FUNCTION test_event_trigger_table_rewrite_oid()
 RETURNS event_trigger
 LANGUAGE plpgsql AS
$$
BEGIN
  RAISE NOTICE 'rewriting table % for reason %',
                pg_event_trigger_table_rewrite_oid()::regclass,
                pg_event_trigger_table_rewrite_reason();
END;
$$;

CREATE EVENT TRIGGER test_table_rewrite_oid
                  ON table_rewrite
   EXECUTE FUNCTION test_event_trigger_table_rewrite_oid();

but the above code only captures time when table DDL is changed.Want it to happen at inserts /deletes

dpsg
  • 1
  • 2
  • You could create a single generic trigger function, but you would still need for each table a trigger – Frank Heikens Feb 10 '23 at 07:43
  • You could set [`log_statement=mod`](https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT). – Zegarek Feb 10 '23 at 08:24
  • @FrankHeikens :Inside the generic trigger function ,how to we get the table name?Can you please help on the same? – dpsg Feb 10 '23 at 10:11
  • You can read [`TG_TABLE_NAME`](https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER) variable that'll be visible inside the trigger. – Zegarek Feb 10 '23 at 10:13
  • That would be TG_TABLE_NAME and TG_TABLE_SCHEMA, or just TG_RELID. See the manual: https://www.postgresql.org/docs/current/plpgsql-trigger.html – Frank Heikens Feb 10 '23 at 10:13
  • @Zegarek Thanks solution worked,one trigger function linked to multiple table ... – dpsg Feb 10 '23 at 13:22

1 Answers1

0

To capture the time when DML statements are run, use a trigger AFTER INSERT OR UPDATE OR DELETE defined FOR EACH STATEMENT.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263