Postgresql trigger for all tables that include create date

Question

I have 54 tables in my postgresql databse. And most of tables includes date of creation time the data. I want to create a global trigger for all tables which include CreatedDate column. And this trigger will update the column when a record inserted.

Answer 1

Every table needs its own trigger, but you can use a single trigger function:

CREATE FUNCTION set_created_date() RETURNS trigger AS $$
BEGIN
  NEW.createddate := CURRENT_TIME;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

For each table you should have a trigger like this:

CREATE TRIGGER set_createddate
BEFORE INSERT ON <table name>
FOR EACH ROW EXECUTE PROCEDURE set_created_date();

You can set the trigger for all 54 tables with this code (first create the trigger function):

DO $$
DECLARE
    t record;
BEGIN
    FOR t IN 
        SELECT * FROM information_schema.columns
        WHERE column_name = 'createddate'
    LOOP
        EXECUTE format('CREATE TRIGGER set_createddate
                        BEFORE INSERT ON %I.%I
                        FOR EACH ROW EXECUTE PROCEDURE set_created_date()',
                        t.table_schema, t.table_name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

This obviously assumes that there are no other tables with a column named createddate. This you can check easily by querying the information_schema.columns table.

Answer 2

For those who have some problems with Patrick's solution. Here the same code with some improvement :

DO $$
DECLARE
    t text;
BEGIN
    FOR t IN 
        SELECT table_name FROM information_schema.columns
        WHERE column_name = 'createddate'
    LOOP
        EXECUTE format('CREATE TRIGGER set_createddate
                        BEFORE INSERT ON %I
                        FOR EACH ROW EXECUTE PROCEDURE set_created_date()',
                        t);
    END LOOP;
END;
$$ LANGUAGE plpgsql;