Change owner for a Postgres DB (all relations)

adjustowner.sql

- https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql
-- I like this one since it modifies tables, views, sequences and functions owner of a certain schema in one go (in one sql statement), without creating a function
DO $$DECLARE r record;
DECLARE
    v_schema varchar := 'public';
    v_new_owner varchar := 'bn_redash';
    text_var1 varchar := '';
    text_var2 varchar := '';
    text_var3 varchar := '';
BEGIN
    FOR r IN
        select 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.tables where table_schema = v_schema
        union all
        select 'ALTER SEQUENCE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.sequences where sequence_schema = v_schema
        union all
        select 'ALTER VIEW "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.views where table_schema = v_schema
        union all
        select 'ALTER FUNCTION "'||nsp.nspname||'"."'||p.proname||'"('||pg_get_function_identity_arguments(p.oid)||') OWNER TO ' || v_new_owner || ';' as a from pg_proc p join pg_namespace nsp ON p.pronamespace = nsp.oid where nsp.nspname = v_schema
        union all
        select 'ALTER SCHEMA "' || v_schema || '" OWNER TO ' || v_new_owner
        union all
        select 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner
    LOOP
      BEGIN
        EXECUTE r.a;
      EXCEPTION WHEN OTHERS THEN
        -- no real exception handling, just silencing and continue with the next statement
        GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
      END;
    END LOOP;
END$$;