PostgreSQL批量修改函數擁有者的操作

Posted on by

Postgresql如何批量修改函數擁有者，默認創建的函數對象的擁有者為當前創建的用戶，如果使用postgres超級管理員創建一個test()的函數，擁有者就是postgres用戶。下面講解下如何批量修改擁有者。

本文演示的Postgresql版本如下：

PostgreSQL 9.6.8

相關視圖

要查詢Postgresql的函數和函數參數需要使用函數視圖和參數視圖，分別記錄瞭函數信息和參數列表信息。

視圖一： information_schema.routines

視圖routines包含當前數據庫中所有的函數。隻有那些當前用戶能夠訪問（作為擁有者或具有某些特權）的函數才會被顯示。需要用到的列如下，完整視圖講解請參考官方文檔。

名稱 數據類型 備註
specific_schema sql_identifier 包含該函數的模式名
routine_name sql_identifier 該函數的名字（在重載的情況下可能重復）
specific_name sql_identifier 該函數的”專用名”。這是一個在模式中唯一標識該函數的名稱，即使該函數真正的名稱已經被重載。專用名的格式尚未被定義，它應當僅被用來與指定例程名稱的其他實例進行比較。

視圖二： information_schema.parameters

視圖parameters包含當前數據庫中所有函數的參數的有關信息。隻有那些當前用戶能夠訪問（作為擁有者或具有某些特權）的函數才會被顯示。需要用到的列如下，完整視圖講解請參考官方文檔。

名稱 數據類型 備註
parameter_name sql_identifier 參數名，如果參數沒有名稱則為空
udt_name sql_identifier 該參數的數據類型的名字
ordinal_position cardinal_number 該參數在函數參數列表中的順序位置（從 1 開始計數）
specific_name cardinal_number 該函數的”專用名”。詳見第 35.40 節。

註意：可以通過routines. specific_name 和 parameters.specific_name字段關聯查詢。

單個修改

如果需要修改的函數隻有一個，請執行如下SQL語句即可：

如果需要修改的函數隻有一個，請執行如下SQL語句即可：

// 無參數函數
ALTER FUNCTION "abc"."test"() OWNER TO "dbadmin";
//帶參數函數
ALTER FUNCTION "abc"."test3"(p1 varchar, p2 varchar) OWNER TO "dbadmin";

批量修改

首先可以查詢當前模式下函數的所有者分別是哪個用戶，使用下面SQL來查詢：

SELECT 
n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
pg_catalog.pg_get_userbyid(p.proowner) as "Owner"
FROM pg_catalog.pg_proc p
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
  LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE pg_catalog.pg_function_is_visible(p.oid)
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
ORDER BY 1, 2;

當前顯示模式“abc”有2個無參函數和1個帶參函數，擁有者都是postgres超級用戶。

然後根據上面講的兩個視圖： routines 和 parameters關聯查詢出模式下的所有函數和參數（目的是為瞭拼接SQL語句），參考如下：

SELECT 
"routines".specific_schema,
"routines".routine_name, 
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回參數中的第一個非null的值
COALESCE("parameters".udt_name, '') AS udt_name,
COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,
"parameters".ordinal_position 
FROM "information_schema"."routines"
LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name 
WHERE "routines".specific_schema='abc' 
ORDER BY 1,2,6;

這裡我們再使用聚合函數: string_agg 把字段 params所有行連接成字符串，並用逗號分隔符分隔。

WITH tmp AS (SELECT 
"routines".specific_schema,
"routines".routine_name, 
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回參數中的第一個非null的值
COALESCE("parameters".udt_name, '') AS udt_name,
COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,
"parameters".ordinal_position 
FROM "information_schema"."routines"
LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name 
WHERE "routines".specific_schema='abc' 
ORDER BY 1,2,6) 
SELECT 
specific_schema, 
routine_name, 
string_agg(params, ',') AS params, 
'"'||specific_schema||'"."'||routine_name||'"('||string_agg(params, ',')||')' AS fname 
FROM tmp GROUP BY specific_schema, routine_name;

最後使用一個Postgres執行代碼片段完成批量修改，完整SQL如下：

DO $$
DECLARE r record;
BEGIN
FOR r IN
WITH tmp AS (SELECT 
"routines".specific_schema,
"routines".routine_name, 
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回參數中的第一個非null的值
COALESCE("parameters".udt_name, '') AS udt_name,
COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,
"parameters".ordinal_position 
FROM "information_schema"."routines"
LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name 
WHERE "routines".specific_schema='abc' 
ORDER BY 1,2,6) SELECT '"'||specific_schema||'"."'||routine_name||'"('||string_agg(params, ',')||')' AS fname FROM tmp GROUP BY specific_schema, routine_name
LOOP
EXECUTE 'ALTER FUNCTION '|| r.fname||' OWNER TO "dbadmin" ';
END LOOP;
END $$;

可以看到模式“abc”的Owner已經全部改為dbadmin這個賬號瞭。

上次批量修改函數可能存在部分特殊場景會報錯， 會把“參數類型” + “返回類型” 拼接在一起

改進方法：我們通過pg_catalog目錄來實現批量修改，參考代碼如下：

DO $$
DECLARE r record;
BEGIN
FOR r IN
 WITH tmp AS (
 SELECT n.nspname as "Schema",
 p.proname as "Name",
 pg_catalog.pg_get_function_result(p.oid) as "Result data type",
 pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
 WHEN p.proisagg THEN 'agg'
 WHEN p.proiswindow THEN 'window'
 WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
 ELSE 'normal'
 END as "Type"
 FROM pg_catalog.pg_proc p
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 WHERE n.nspname = 'etl'
 ORDER BY 1, 2, 4
 ) SELECT '"' || "Schema" || '"' || '.' || '"' || "Name" || '"' || '(' || "Argument data types" ||')' AS fname FROM tmp
LOOP
EXECUTE 'ALTER FUNCTION '|| r.fname||' OWNER TO "postgres" ';
END LOOP;
END $$;

補充：PostgreSQL更改Owner所有者

網上一個大神寫的

SELECT
‘alter table ' || nsp.nspname || ‘.' || cls.relname || ' owner to usr_zhudong;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
WHERE
nsp.nspname IN ( ‘public' )
AND cls.relnamespace = nsp.oid
AND cls.relkind = ‘r'
ORDER BY
nsp.nspname,
cls.relname;

我來做一個改版

SELECT
'alter table ' || nsp.nspname || '.' || cls.relname || ' owner to test2;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
WHERE
nsp.nspname IN ( 'public' )
AND cls.relnamespace = nsp.oid
AND cls.relkind = 'r'
ORDER BY
nsp.nspname,
cls.relname;
 
SELECT
'alter table "' || nsp.nspname || '"."' || cls.relname || '" owner to user01;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
WHERE
nsp.nspname IN ( 'public' )
AND cls.relnamespace = nsp.oid
AND cls.relkind = 'r'
ORDER BY
nsp.nspname,
cls.relname;

效果：

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：