Return to Revisions

You can use the event trigger machinery to achieve your goal.

Say, you have a table in the noalter schema, and want to show which modifications took place. First, you need an event trigger function:

CREATE OR REPLACE FUNCTION test.tr_alterbla()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
DECLARE obj record;
        q text;
BEGIN 
    q := current_query();
    RAISE WARNING '%', q;
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE EXCEPTION 'This happened: % %', tg_tag, obj.objid::regclass;
    END LOOP; 
END;
$function$
;

For testing, this will prevent altering tables by raising an error, in which telling the type of operation and the table name. Additionally, it will emit the query that initiated the table change. While the obj record would return everything about the triggering query in its command column, there is no out-of-the-box way to inspect it deeper (or just show it), so you have to parse the output of current_query(), unfortunately.

With all this, you should be able to change the function body to do your UPDATE.

Now what remains is creating the event trigger:

CREATE EVENT TRIGGER show_alter 
    ON ddl_command_end WHEN TAG IN ('ALTER TABLE') 
    EXECUTE PROCEDURE tr_alterbla();

This reduces the scope of the trigger to ALTER TABLE only. Unfortunately, there is no way of further focusing on a single table - that's why the obj.objid::regclass bit is present in my example function.

Note: if you are really changing functions, you have to specify CREATE FUNCTION in the WHEN clause above. I'm afraid in this case it will be (much) harder to figure out which check of which table is affected - that might be worth a separate question here.