您可以在PostgreSQL 9中尝试以下操作

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'alter table '|| r.tablename ||' owner to newowner;';
    END LOOP;
END$$;

从其他人的讨论来看，他们不同意我的问题。如前所述，如果所有者是postgres, REASSIGN OWNED不工作。所以，受之前讨论的启发，我写了这个脚本:

CREATE OR REPLACE FUNCTION public.alt_own_onall (v_new_owner text)
    RETURNS void
    LANGUAGE plpgsql
AS $alt_own_onall$
-- ALTer OWNer ON ALL objects
DECLARE
    r           RECORD;
    v_sqlcmd    TEXT; -- commande SQL
    b_modif     BOOLEAN DEFAULT false;    -- si au moins une modif
BEGIN
    v_new_owner := quote_ident (v_new_owner);
    IF v_new_owner NOT IN
        (SELECT role_name FROM information_schema.enabled_roles WHERE role_name <>'postgres')
        THEN
            RAISE WARNING '[%] est inconnu', v_new_owner;
            RETURN;
    END IF
    ; -- tables
    RAISE INFO 'Le nouveau propriétaire des tables, vues, fonctions, schémas et de la base va être [%]', v_new_owner
    ;
    FOR r IN
        SELECT quote_ident(schemaname) || '.' || quote_ident(tablename) sch_tbl
        FROM pg_catalog.pg_tables
        WHERE schemaname !~'^(pg_|information)' AND tableowner <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER TABLE ' || r.sch_tbl || ' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ; -- views
    FOR r IN
        SELECT quote_ident(schemaname) || '.' || quote_ident(viewname) v_sch_nam
        FROM pg_catalog.pg_views
        WHERE schemaname !~'^(pg_|information)' AND viewowner <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER VIEW '|| r.v_sch_nam ||' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ; -- séquences
/*
    ERROR:  cannot change owner of sequence "*_seq"
    DETAIL:  Sequence "*_seq" is linked to table "*".

    FOR r IN
        SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) s_sch_nam
        FROM information_schema.sequences
        WHERE sequence_schema !~'^(pg_|information)'
    LOOP
        EXECUTE 'ALTER SEQUENCE ' || r.s_sch_nam || ' OWNER TO ' || v_new_owner || ';'
        ;
    END LOOP
    ; -- fonctions */
    FOR r IN
        SELECT
            quote_ident (n.nspname) || '.' || quote_ident (p.proname) || '(' ||
            pg_get_function_identity_arguments (p.oid) || ')' AS nsp_pro_arg
        FROM pg_proc AS p
            JOIN pg_namespace   AS n ON p.pronamespace    = n.oid
            JOIN pg_authid      AS a ON p.proowner        = a.oid
        WHERE n.nspname !~'^(pg_|information)' AND quote_ident(a.rolname) <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER FUNCTION ' || r.nsp_pro_arg || ' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ; -- schémas
    FOR r IN
        SELECT quote_ident(schema_name) s_nam
        FROM information_schema.schemata
        WHERE schema_name !~'^(pg_|information)' AND quote_ident(schema_owner) <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER SCHEMA ' || r.s_nam || ' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ; -- base
    FOR r IN
        SELECT d.datname, a.rolname
        FROM pg_database d JOIN pg_authid a ON d.datdba=a.oid
        WHERE d.datname=current_database() AND a.rolname <> v_new_owner
    LOOP
        v_sqlcmd := 'ALTER DATABASE ' || quote_ident(current_database()) || ' OWNER TO ' || v_new_owner || ';';
        RAISE INFO 'Exécution de [%]', v_sqlcmd;
        EXECUTE v_sqlcmd;
        b_modif := true;
    END LOOP
    ;
    IF NOT b_modif THEN RAISE INFO 'Aucune modification n''a été effectuée.'; END IF;
END
;$alt_own_onall$;

在PSQL下使用它: SELECT alt_own_onall ('new_role_name'); 你必须把法语信息翻译成你自己的语言。 适用于pg 9.5

根据elysch的回答，这里有一个针对多个模式的解决方案:

DO $$
DECLARE 
  r record;
  i int;
  v_schema text[] := '{public,schema1,schema2,schema3}';
  v_new_owner varchar := 'my_new_owner';
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 = ANY (v_schema)
        union all
        select 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.sequences where sequence_schema = ANY (v_schema)
        union all
        select 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.views where table_schema = ANY (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 = ANY (v_schema)
        union all
        select 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner 
    LOOP
        EXECUTE r.a;
    END LOOP;
    FOR i IN array_lower(v_schema,1) .. array_upper(v_schema,1)
    LOOP
        EXECUTE 'ALTER SCHEMA "' || v_schema[i] || '" OWNER TO ' || v_new_owner ;
    END LOOP;
END
$$;

与@AlexSoto的函数方法相同:

IFS=$'\n'  
for fnc in `psql -qAt -c "SELECT  '\"' || p.proname||'\"' || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'public';" YOUR_DB` ; do  psql -c "alter function $fnc owner to NEW_OWNER" YOUR_DB; done

非常简单

Su - postgres psql 重新分配[old_user]到[new_user]; \c[你的资料库] 重新分配[old_user]到[new_user];

完成了。

如果希望在一条sql语句中完成，则需要定义一个exec()函数，如http://wiki.postgresql.org/wiki/Dynamic_DDL中所述

CREATE FUNCTION exec(text) returns text language plpgsql volatile
  AS $f$
    BEGIN
      EXECUTE $1;
      RETURN $1;
    END;
$f$;

然后你可以执行这个查询，它会改变表，序列和视图的所有者:

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' ||
            quote_ident(s.relname) || ' OWNER TO $NEWUSER')
  FROM (SELECT nspname, relname
          FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
         WHERE nspname NOT LIKE E'pg\\_%' AND 
               nspname <> 'information_schema' AND 
               relkind IN ('r','S','v') ORDER BY relkind = 'S') s;

$NEWUSER是新所有者的postgresql新名称。

在大多数情况下，您需要超级用户才能执行此操作。可以通过将所有者从您自己的用户更改为您所属的角色组来避免这种情况。

感谢RhodiumToad在#postgresql上的帮助。