Postgresql, how to add multiple table for one trigger

Question

I have a question: how to add multiple table for one trigger? Is that possible, or should I just make another 2 triggers for 2 different tables?

Answer 1

Create a new function

CREATE OR REPLACE FUNCTION updated_timestamp_func()
RETURNS TRIGGER
LANGUAGE plpgsql AS
'
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
';

Then create a trigger for each table that has the column updated_at

DO $$
DECLARE
    t text;
BEGIN
    FOR t IN
        SELECT table_name FROM information_schema.columns WHERE column_name = 'updated_at'
    LOOP
        EXECUTE format('CREATE TRIGGER trigger_update_timestamp
                    BEFORE UPDATE ON %I
                    FOR EACH ROW EXECUTE PROCEDURE updated_timestamp_func()', t,t);
    END loop;
END;
$$ language 'plpgsql';