PostgreSQL批量修改函数拥有者

一时失言乱红尘 2022-04-23 03:10 110阅读 0赞

Postgresql如何批量修改函数拥有者，默认创建的函数对象的拥有者为当前创建的用户，如果使用postgres超级管理员创建一个test()的函数，拥有者就是postgres用户。下面讲解下如何批量修改拥有者。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMTU4Mzc2_size_16_color_FFFFFF_t_70]  
本文演示的Postgresql版本如下：  
PostgreSQL 9.6.8

## 相关视图 ##

要查询Postgresql的函数和函数参数需要使用函数视图和参数视图，分别记录了函数信息和参数列表信息。

视图一： information\_schema.routines  
视图routines包含当前数据库中所有的函数。只有那些当前用户能够访问（作为拥有者或具有某些特权）的函数才会被显示。需要用到的列如下，完整视图讲解请参考官方文档。

<table> 
 <thead> 
  <tr> 
   <th align="left">名称</th> 
   <th align="left">数据类型</th> 
   <th align="left">备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">specific_schema</td> 
   <td align="left">sql_identifier</td> 
   <td align="left">包含该函数的模式名</td> 
  </tr> 
  <tr> 
   <td align="left">routine_name</td> 
   <td align="left">sql_identifier</td> 
   <td align="left">该函数的名字（在重载的情况下可能重复）</td> 
  </tr> 
  <tr> 
   <td align="left">specific_name</td> 
   <td align="left">sql_identifier</td> 
   <td align="left">该函数的"专用名"。这是一个在模式中唯一标识该函数的名称，即使该函数真正的名称已经被重载。专用名的格式尚未被定义，它应当仅被用来与指定例程名称的其他实例进行比较。</td> 
  </tr> 
 </tbody> 
</table>

视图二： information\_schema.parameters  
视图parameters包含当前数据库中所有函数的参数的有关信息。只有那些当前用户能够访问（作为拥有者或具有某些特权）的函数才会被显示。需要用到的列如下，完整视图讲解请参考官方文档。

<table> 
 <thead> 
  <tr> 
   <th align="left">名称</th> 
   <th align="left">数据类型</th> 
   <th align="left">备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">parameter_name</td> 
   <td align="left">sql_identifier</td> 
   <td align="left">参数名，如果参数没有名称则为空</td> 
  </tr> 
  <tr> 
   <td align="left">udt_name</td> 
   <td align="left">sql_identifier</td> 
   <td align="left">该参数的数据类型的名字</td> 
  </tr> 
  <tr> 
   <td align="left">ordinal_position</td> 
   <td align="left">cardinal_number</td> 
   <td align="left">该参数在函数参数列表中的顺序位置（从 1 开始计数）</td> 
  </tr> 
  <tr> 
   <td align="left">specific_name</td> 
   <td align="left">cardinal_number</td> 
   <td align="left">该函数的"专用名"。详见第 35.40 节。</td> 
  </tr> 
 </tbody> 
</table>

注意：可以通过routines. specific\_name 和 parameters.specific\_name字段关联查询。

## 单个修改 ##

如果需要修改的函数只有一个，请执行如下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超级用户。  
![在这里插入图片描述][20190123150540323.png]

然后根据上面讲的两个视图： 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;

![在这里插入图片描述][20190123153154160.png]

这里我们再使用聚合函数: 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;

![在这里插入图片描述][20190123153303945.png]  
最后使用一个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这个账号了。  
![在这里插入图片描述][20190123161034624.png]  
上次批量修改函数可能存在部分特殊场景会报错， 会把“参数类型” + “返回类型” 拼接在一起

改进方法：我们通过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 $$;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMTU4Mzc2_size_16_color_FFFFFF_t_70]: /images/20220323/1350f40ed6174dfb909d0b107236aa2a.png
[20190123150540323.png]: /images/20220323/df6bcc8e93454beb923e70ed3befb937.png
[20190123153154160.png]: /images/20220323/25c090bbe8eb4199b784462b01aebeb4.png
[20190123153303945.png]: /images/20220323/f18c41240661409dad2e0b354629c1f9.png
[20190123161034624.png]: /images/20220323/b3a5dd938e974303a4188063c79af48e.png