有沒有辦法同時設置 PostgreSQL 數據庫中所有對象的所有者？

Stack Overflow Q & A Modify OWNER on all tables 同時在 PostgreSQL中描述了一些將表和其他對象更改為特定使用者的絕妙方法，並且它工作得很好，但是所有建議似乎都忽略了我創建的函式。

是否有一種相當簡單的方法來重置數據庫中所有對象的所有者，包括函式？手工操作是非常不可取的。

如果您確切地知道自己在做什麼，那麼您應該只直接**作業系統目錄。**它可能會產生意想不到的副作用。或者，您可能會損壞數據庫（或整個數據庫集群）而無法修復。

傑里米的回答雖然基本上可以解決問題，但不建議公眾使用。它無條件地更改模式中的所有功能。您確定沒有影響系統功能或附加模組安裝的功能嗎？

更改已經屬於指定所有者的功能的所有者也是沒有意義的。

首先，檢查是否REASSIGN OWNED適合您：

更改數據庫角色擁有的數據庫對象的所有權

您必須列出所有要明確拒絕的角色。但它也重新分配了功能。

將給定模式中的所有函式（而不是其他對象）分配給新所有者（可選地，不管以前的所有者）：

SELECT string_agg('ALTER FUNCTION '|| oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc
WHERE  pronamespace = 'public'::regnamespace
-- AND relowner <> 'foo'::regrole
-- AND proname ~~ 'f_%'

這會生成規範的 SQL 命令ALTER FUNCTION ...來更改所有函式（在指定的模式中）。在執行之前檢查命令 - 一個一個或一次全部：

ALTER FUNCTION public.bar(text, text) OWNER TO foo;
ALTER FUNCTION public.foo(x integer) OWNER TO foo;
...

轉換 toregprocedure產生一個帶參數的有效函式名，在必要時用雙引號括起來，在必要時為目前search_path.

為了簡單起見，還使用對象標識符類型regnamespace``regrole。

WHERE我添加了一些您可能想要用來過濾結果的註釋子句。

您可以將所有這些放入DO語句或函式中，如相關答案中所示：

• 刪除命名空間中的所有函式？（執行生成的 DDL 命令？）

對於 Postgres 9.4 或更早版本：

SELECT string_agg('ALTER FUNCTION ' || oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public'
-- AND p.relowner <> (SELECT oid FROM pg_roles WHERE rolname = 'foo')
-- AND p.proname ~~ 'f_%'

聚合函式string_agg()需要 PostgreSQL 9.0 或更高版本。在舊版本中替換為array_agg()and array_to_string()。

我使用此函式來更改表、函式、類型等的所有者。您可以更改游標的查詢以使其適應您的需要。

CREATE OR REPLACE FUNCTION fn_setowner(varchar(50), boolean) RETURNS void AS
$BODY$
DECLARE
p_owner ALIAS FOR $1;
p_debug ALIAS FOR $2;
v_i integer := 0;
v_sql text;

--  CURSORS
-- SCHEMA
pesquemas CURSOR FOR
   SELECT quote_ident(schema_name) as nombre_esquema from information_schema.schemata WHERE schema_name NOT LIKE 'pg_%'
   and schema_name NOT IN ('information_schema') ORDER BY 1 ASC;

-- TABLE
ptablas CURSOR FOR
   SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) as nombre_tabla, * FROM information_schema.tables
   WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
   AND table_type <> 'FOREIGN TABLE' ORDER BY 1 ASC;

-- FUNCTION
pfunciones CURSOR FOR
   SELECT quote_ident(b.nspname) || '.' || quote_ident(a.proname) || '(' || pg_catalog.oidvectortypes(a.proargtypes) || ')' as nombre_function 
   FROM pg_proc a  INNER JOIN pg_namespace b on a.pronamespace = b.oid 
   WHERE b.nspname NOT IN ('pg_catalog', 'information_schema') AND proisagg = 'f'
   AND a.proname not like 'fsym_%' AND a.proname not like 'dblink%' ORDER BY 1 ASC;

-- SEQUENCE
psecuencias CURSOR FOR
   SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) as nombre_secuencia FROM information_schema.sequences
   WHERE sequence_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY 1 ASC;

-- TYPE
ptipos CURSOR FOR
   SELECT quote_ident(n.nspname) || '.' || quote_ident(t.typname) as nombre_tipo
   FROM pg_type t
   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace 
   WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) 
   AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
   AND n.nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY 1 ASC;


BEGIN
--  CHECK LOGIN
   IF NOT EXISTS (SELECT 1 FROM pg_user WHERE usename = p_owner) THEN                     
       RAISE EXCEPTION 'Login role not exists --> %', p_owner
           USING HINT = 'Please specify correct login and try again.';
   END IF;

   v_i = 0;
   if (p_debug) THEN
   RAISE NOTICE '--########## CHANGE SCHEMA OWNER ##########--';
   END IF;
   FOR resquema IN pesquemas LOOP
       v_sql = 'ALTER SCHEMA ' || resquema.nombre_esquema || ' OWNER TO ' || quote_ident(p_owner) || ';';
       if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
       EXECUTE v_sql;
       v_i = v_i + 1;
   END LOOP;
   if (p_debug) THEN
   RAISE NOTICE '--@@@@@@ SCHEMAS WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
   END IF;

   v_i = 0;
   if (p_debug) THEN
   RAISE NOTICE '--########## CHANGE TABLE OWNER ##########--';
   END IF;
   FOR rtables IN  ptablas LOOP
       v_sql = 'ALTER TABLE ' || rtables.nombre_tabla || ' OWNER TO ' || quote_ident(p_owner) || ';';
       if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
       EXECUTE v_sql;
       v_i = v_i + 1;
   END LOOP;
   if (p_debug) THEN
   RAISE NOTICE '--@@@@@@ TABLES WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
   END IF;

   v_i = 0;
   if (p_debug) THEN
   RAISE NOTICE '--########## CHANGE FUNCTION OWNER ##########--';
   END IF;
   FOR rfunction IN  pfunciones LOOP
       v_sql = 'ALTER FUNCTION ' || rfunction.nombre_function || ' OWNER TO ' || quote_ident(p_owner) || ';';
       if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
       EXECUTE v_sql;
       v_i = v_i + 1;
   END LOOP;
   if (p_debug) THEN
   RAISE NOTICE '--@@@@@@ FUNCTIONS WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
   END IF;

   v_i = 0;
   if (p_debug) THEN
   RAISE NOTICE '--########## CHANGE SEQUENCE OWNER ########## --';
   END IF;
   FOR rsecuencias IN  psecuencias LOOP
       v_sql = 'ALTER TABLE ' || rsecuencias.nombre_secuencia || ' OWNER TO ' || quote_ident(p_owner) || ';';             
       if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
       EXECUTE v_sql;
       v_i = v_i + 1;
   END LOOP;
   if (p_debug) THEN
   RAISE NOTICE '--@@@@@@ SEQUENCES WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
   END IF;

   v_i = 0;
   if (p_debug) THEN
   RAISE NOTICE '--########## CHANGE TYPE OWNER ##########--';
   END IF;
   FOR rtipos IN  ptipos LOOP                
       v_sql = 'ALTER TYPE ' || rtipos.nombre_tipo || ' OWNER TO ' || quote_ident(p_owner) || ';';                
       if (p_debug) THEN RAISE NOTICE '%', v_sql; END IF;
       EXECUTE v_sql;
       v_i = v_i + 1;
   END LOOP;
   if (p_debug) THEN
   RAISE NOTICE '--@@@@@@  TYPES WITH OWNER % TOTAL = % @@@@@@--', p_owner, CAST(v_i AS VARCHAR);
   END IF;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;

然後我就執行（如果要調試輸出只需將第二個參數設置為 true）：

SELECT fn_setowner('demo', false);
DROP FUNCTION fn_setowner(varchar(30), boolean);

引用自：https://dba.stackexchange.com/questions/9708