Change owner of postgres db objects

change-owner.sql

DO $$ DECLARE
    r RECORD;
    target_user text := 'target-user';
    statement text;
BEGIN
    -- table
    FOR r IN (
        SELECT tablename as entry
        FROM pg_tables
        WHERE
            schemaname = current_schema())
    LOOP
        statement := 'ALTER TABLE ' || quote_ident(r.entry) || ' OWNER TO ' || quote_ident(target_user);
        RAISE NOTICE 'statement: %', statement;
        EXECUTE statement;
    END LOOP;

    -- sequence
    FOR r IN (
        SELECT sequencename as entry
        FROM pg_sequences
        WHERE
            schemaname = current_schema())
    LOOP
        statement := 'ALTER SEQUENCE ' || quote_ident(r.entry) || ' OWNER TO ' || quote_ident(target_user);
        RAISE NOTICE 'statement: %', statement;
        EXECUTE statement;
    END LOOP;
    
    -- view
    FOR r IN (
        SELECT viewname as entry
        FROM pg_views
        WHERE
            schemaname = current_schema())
    LOOP
        statement := 'ALTER VIEW ' || quote_ident(r.entry) || ' OWNER TO ' || quote_ident(target_user);
        RAISE NOTICE 'statement: %', statement;
        EXECUTE statement;
    END LOOP;
    
END $$;