PostgreSQL批量修改函数拥有者( 二 ) _函数

最后使用一个执行代码片段完成批量修改，完整SQL如下：
DO $$DECLARE r record;BEGINFOR r INWITH 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 fnameFROM tmp GROUP BY specific_schema, routine_nameLOOPEXECUTE 'ALTER FUNCTION '|| r.fname||' OWNER TO "dbadmin" ';END LOOP;END $$;
可以看到模式“abc”的Owner已经全部改为这个账号了。
上次批量修改函数可能存在部分特殊场景会报错，会把“参数类型” + “返回类型” 拼接在一起
改进方法：我们通过目录来实现批量修改，参考代码如下：
DO $$DECLARE r record;BEGINFOR r INWITH 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",CASEWHEN 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 pLEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespaceWHERE n.nspname = 'etl'ORDER BY 1, 2, 4) SELECT '"' || "Schema" || '"' || '.' || '"' || "Name" || '"'|| '(' || "Argument data types"||')'AS fnameFROM tmpLOOPEXECUTE 'ALTER FUNCTION '|| r.fname||' OWNER TO "postgres" ';END LOOP;END $$;