Fire a trigger function when checks change

Question

I have the following use case:

I have a set of different checks on different tables in one schema. If a data set is 'wrong' (did not pass a check) it gets pushed into another table in another schema. There one can edit the data again and, via an AFTER UPDATE trigger, the data set will be pushed back to the original table. This will be successful when no check is violated.

Now I have the problem that I sometimes need to edit my check functions, because some data set might have been wrongfully pushed away from the table. But the data which still lay in the 'error' table will not get pushed back into the original table before I trigger an UPDATE on it, even though it might be correct after my edition of the check function.

My question is: Is it possible to automatically trigger an UPDATE on all data sets in such an 'error' table after editing the check functions of the original table (for instance based on the error message)?

Answer 1

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.