snippetsqlMinor

postgresql: how to know when a function was modified

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

postgresqlknowhowfunctionmodifiedwhenwas

Problem

Scenario:
Someone creates a function named "foo" in a PostgreSQL database.
Next, someone modifies the inner workings of function "foo".

Question:
Does PostgreSQL offer a means to know who modified function "foo" and when "foo" was modified?

Solution

Sure, you can use EVENT TRIGGERS and you can create them on any of these DDL events listed here

CREATE OR REPLACE FUNCTION abort_any_command()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $
BEGIN
  RAISE EXCEPTION '[%] user % attempt command % which disabled',
    current_timestamp,
    current_user,
    tg_tag;
END;
$;

CREATE EVENT TRIGGER abort_ddl ON ddl_command_start
  WHEN TAG IN ('DROP FUNCTION', 'CREATE FUNCTION')
  EXECUTE PROCEDURE abort_any_command();

You can create the EVENT TRIGGER on ddl_command_end if you want an event to trigger ONLY after the operation has completed successfully. After you create an EVENT TRIGGER, you can try to drop the function and you'll see.

DROP FUNCTION abort_any_command();
ERROR:  [2017-01-09 20:27:33.843529-06] user ecarroll attempt command DROP FUNCTION which disabled

If you just want it in the logs, rather than to raise the exception then use a different level than RAISE EXCEPTION. You should also easily be able to make this INSERT into a table if you'd like.

Code Snippets

CREATE OR REPLACE FUNCTION abort_any_command()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION '[%] user % attempt command % which disabled',
    current_timestamp,
    current_user,
    tg_tag;
END;
$$;

CREATE EVENT TRIGGER abort_ddl ON ddl_command_start
  WHEN TAG IN ('DROP FUNCTION', 'CREATE FUNCTION')
  EXECUTE PROCEDURE abort_any_command();

DROP FUNCTION abort_any_command();
ERROR:  [2017-01-09 20:27:33.843529-06] user ecarroll attempt command DROP FUNCTION which disabled

Context

StackExchange Database Administrators Q#160484, answer score: 7

Revisions (0)

No revisions yet.