bulk_pg_owner_update.sql

--- PGSQL Script: Bulk Update Schema, Table & Sequence Owners

DO $$
DECLARE
    sch RECORD;
    obj RECORD;
    owner_name TEXT := 'dolphin';  -- <-- declare your owner variable here
BEGIN
    -- Loop over all your target schemas
    FOR sch IN
        SELECT nspname
        FROM pg_namespace
        WHERE nspname IN ('schema_1', 'schema_2', 'schema_N')
    LOOP
        -- Change schema owner
        EXECUTE format('ALTER SCHEMA %I OWNER TO %I;', sch.nspname, owner_name);

        -- Change all tables in this schema
        FOR obj IN
            SELECT tablename
            FROM pg_tables
            WHERE schemaname = sch.nspname
        LOOP
            EXECUTE format('ALTER TABLE %I.%I OWNER TO %I;', sch.nspname, obj.tablename, owner_name);
        END LOOP;

        -- Change all sequences in this schema
        FOR obj IN
            SELECT sequence_name
            FROM information_schema.sequences
            WHERE sequence_schema = sch.nspname
        LOOP
            EXECUTE format('ALTER SEQUENCE %I.%I OWNER TO %I;', sch.nspname, obj.sequence_name, owner_name);
        END LOOP;
    END LOOP;
END;
$$;