PostgreSQL Updated at column trigger

updated_at.sql

-- Function to automatically update the updated_at column
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply the trigger to all tables with an updated_at column
CREATE OR REPLACE FUNCTION create_updated_at_trigger(table_name text)
RETURNS void AS $$
BEGIN
    EXECUTE format('
        CREATE TRIGGER update_updated_at_trigger
        BEFORE UPDATE ON %I
        FOR EACH ROW
        EXECUTE FUNCTION update_updated_at_column();
    ', table_name);
END;
$$ LANGUAGE plpgsql;

-- Create triggers for existing tables
SELECT create_updated_at_trigger('users');

Or to run it over all tables in your schema (assuming they have an updated_at column)

DO $$ 
DECLARE
    table_name text;
BEGIN
    FOR table_name IN 
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public'  -- Change this if you're using a different schema
    LOOP
        EXECUTE format('SELECT create_updated_at_trigger(%L)', table_name);
    END LOOP;
END $$;