| 名稱 | 數(shù)據(jù)類型 | 備注 |
|---|---|---|
| specific_schema | sql_identifier | 包含該函數(shù)的模式名 |
| routine_name | sql_identifier | 該函數(shù)的名字（在重載的情況下可能重復） |
| specific_name | sql_identifier | 該函數(shù)的"專用名"。這是一個在模式中唯一標識該函數(shù)的名稱，即使該函數(shù)真正的名稱已經(jīng)被重載。專用名的格式尚未被定義，它應當僅被用來與指定例程名稱的其他實例進行比較。 |
視圖二： information_schema.parameters
視圖parameters包含當前數(shù)據(jù)庫中所有函數(shù)的參數(shù)的有關信息。只有那些當前用戶能夠訪問（作為擁有者或具有某些特權）的函數(shù)才會被顯示。需要用到的列如下，完整視圖講解請參考官方文檔。
| 名稱 | 數(shù)據(jù)類型 | 備注 |
|---|---|---|
| parameter_name | sql_identifier | 參數(shù)名，如果參數(shù)沒有名稱則為空 |
| udt_name | sql_identifier | 該參數(shù)的數(shù)據(jù)類型的名字 |
| ordinal_position | cardinal_number | 該參數(shù)在函數(shù)參數(shù)列表中的順序位置（從 1 開始計數(shù)） |
| specific_name | cardinal_number | 該函數(shù)的"專用名"。詳見第 35.40 節(jié)。 |
注意：可以通過routines. specific_name 和 parameters.specific_name字段關聯(lián)查詢。
如果需要修改的函數(shù)只有一個，請執(zhí)行如下SQL語句即可：
如果需要修改的函數(shù)只有一個，請執(zhí)行如下SQL語句即可：
// 無參數(shù)函數(shù) ALTER FUNCTION "abc"."test"() OWNER TO "dbadmin"; //帶參數(shù)函數(shù) ALTER FUNCTION "abc"."test3"(p1 varchar, p2 varchar) OWNER TO "dbadmin";
首先可以查詢當前模式下函數(shù)的所有者分別是哪個用戶，使用下面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個無參函數(shù)和1個帶參函數(shù)，擁有者都是postgres超級用戶。
然后根據(jù)上面講的兩個視圖： routines 和 parameters關聯(lián)查詢出模式下的所有函數(shù)和參數(shù)（目的是為了拼接SQL語句），參考如下：
SELECT
"routines".specific_schema,
"routines".routine_name,
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回參數(shù)中的第一個非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;
這里我們再使用聚合函數(shù): string_agg 把字段 params所有行連接成字符串，并用逗號分隔符分隔。
WITH tmp AS (SELECT
"routines".specific_schema,
"routines".routine_name,
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回參數(shù)中的第一個非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執(zhí)行代碼片段完成批量修改，完整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返回參數(shù)中的第一個非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已經(jīng)全部改為dbadmin這個賬號了。
上次批量修改函數(shù)可能存在部分特殊場景會報錯， 會把“參數(shù)類型” + “返回類型” 拼接在一起
改進方法：我們通過pg_catalog目錄來實現(xiàn)批量修改，參考代碼如下：
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所有者
網(wǎng)上一個大神寫的
SELECT ‘a(chǎn)lter 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;
效果：
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
