postgresqldatabase-trigger

Postgresql trigger for all tables that include create date

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.

Solution

  • 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;