PostgreSQL 11 如何修改demoproc函数以适配同名不同参数的存储过程

• PostgreSQL 允许存储过程重载（同一模式下同名但参数列表不同），仅通过模式名+存储过程名无法唯一标识目标对象，必须补充参数类型签名才能准确定位到要修改的存储过程
• 你原有代码还存在语法错误：ALTER PROCEDURE %I.%I( owner to %I 语法非法，缺少右括号，也不符合ALTER对象所有者的标准语法规范

这个方案需要额外传入参数类型列表，唯一标识目标存储过程

修改后的函数代码 #

CREATE OR REPLACE FUNCTION public.demoproc(
    i_schemaname character varying, 
    i_objectname character varying, 
    i_arg_types character varying[], -- 新增参数：存储过程的参数类型列表，比如'{integer, varchar}'
    i_t_owner character varying
) RETURNS text
LANGUAGE plpgsql SECURITY DEFINER
AS $$
declare
    v_statement text ;
    v_arg_sign text;
begin
    -- 拼接参数类型签名，比如 integer, character varying
    SELECT string_agg(trim(both ' ' from arg_type), ',') INTO v_arg_sign
    FROM unnest(i_arg_types) arg_type;

    -- 修正ALTER语法，拼接完整的存储过程标识
    v_statement := FORMAT(
        'ALTER PROCEDURE %I.%I(%s) OWNER TO %I',
        i_schemaname,
        i_objectname,
        v_arg_sign,
        i_t_owner
    );
    EXECUTE v_statement;

    return FORMAT('PROCEDURE "%I"."%I"(%s) 所有者已修改为 "%I"',
        i_schemaname,
        i_objectname,
        v_arg_sign,
        i_t_owner
    );
END;
$$;

调用示例 #

要修改schema_name下参数为int, varchar的demo_proc存储过程的所有者为new_user，调用方式如下：

select public.demoproc('schema_name', 'demo_proc', '{integer, character varying}', 'new_user');

如果不需要精准指定，想一次性修改同一模式下同名称所有重载存储过程的所有者，可以用这个版本，不需要调整原有调用逻辑

修改后的函数代码 #

CREATE OR REPLACE FUNCTION public.demoproc(
    i_schemaname character varying, 
    i_objectname character varying, 
    i_t_owner character varying
) RETURNS text
LANGUAGE plpgsql SECURITY DEFINER
AS $$
declare
    v_proc_record record;
    v_modified_count integer := 0;
begin
    -- 遍历pg_proc系统表，匹配所有符合名称和模式的存储过程
    FOR v_proc_record IN 
        SELECT proname, pronargs, pg_get_function_arguments(p.oid) as arg_sign
        FROM pg_proc p
        JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE n.nspname = i_schemaname
          AND p.proname = i_objectname
          AND p.prokind = 'p' -- 只匹配存储过程，排除普通函数
    LOOP
        EXECUTE FORMAT(
            'ALTER PROCEDURE %I.%I(%s) OWNER TO %I',
            i_schemaname,
            i_objectname,
            v_proc_record.arg_sign,
            i_t_owner
        );
        v_modified_count := v_modified_count + 1;
    END LOOP;

    return FORMAT('共修改 %s 个名为 "%I"."%I" 的存储过程，所有者已全部修改为 "%I"',
        v_modified_count,
        i_schemaname,
        i_objectname,
        i_t_owner
    );
END;
$$;

调用示例 #

调用方式和你原来的完全一致：

select public.demoproc('schema_name', 'function_name', 'username');

内容的提问来源于stack exchange，提问作者Adam Mulla