您可以在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$$;

http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php也是一个很好的快速解决方案，适用于一个数据库中的多个模式:

表

SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO my_new_owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

序列

SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

的观点

SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO my_new_owner;'
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

物化视图

基于这个答案

SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;'
FROM pg_class WHERE relkind = 'm'
ORDER BY oid;

这将生成所有必需的ALTER TABLE / ALTER SEQUENCE / ALTER VIEW语句，复制这些语句并将它们粘贴回plsql以运行它们。

检查你在psql中的工作，执行以下操作:

\dt *.*
\ds *.*
\dv *.*

我最近不得不改变数据库中所有对象的所有权。虽然表、视图、触发器和序列可以很容易地更改，但上述方法对于函数来说失败了，因为签名是函数名的一部分。当然，我有MySQL的背景，不太熟悉Postgres。

然而，pg_dump允许你只转储模式，其中包含ALTER xxx OWNER to yyy;你需要的陈述。下面是我在这个话题上的一点贝壳魔法

pg_dump -s YOUR_DB | grep -i 'owner to' | sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | psqL YOUR_DB

被接受的解决方案不关心函数所有权，下面的解决方案会关心所有的问题(在回顾时，我注意到它类似于上面的@magiconair)

echo "Database: ${DB_NAME}"
echo "Schema: ${SCHEMA}"
echo "User: ${NEW_OWNER}"

pg_dump -s -c -U postgres ${DB_NAME} | egrep "${SCHEMA}\..*OWNER TO"| sed -e "s/OWNER TO.*;$/OWNER TO ${NEW_OWNER};/" | psql -U postgres -d ${DB_NAME}
# do following as last step to allow recovery
psql -U postgres -d postgres -c "ALTER DATABASE ${DB_NAME} OWNER TO ${NEW_OWNER};"

下面这个更简单的shell脚本适合我。

#!/bin/bash
for i in  `psql -U $1  -qt -c  "select tablename from pg_tables where schemaname='$2'"`
do
psql -U $1 -c  "alter table $2.$i set schema $3"
done

其中输入$1 -用户名(数据库) $2 =现有模式 $3 =新模式。

与@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