PostgreSQL 13.13创建带TEXT参数的函数时参数丢失求助

使用PostgreSQL 13.13版本，通过DBGrip工具创建名为delete_user_and_related_data的函数，定义了TEXT类型输入参数p_username，但函数创建成功后参数丢失。调用时提示错误：delete_user_and_related_data(unknown) is not a procedure。尝试过将参数改为varchar类型、添加IN前缀、创建存储过程等操作，均出现相同问题。

原创建语句：

CREATE OR REPLACE FUNCTION delete_user_and_related_data(p_username TEXT)
RETURNS VOID
LANGUAGE plpgsql
as
$$
DECLARE
    v_uid INTEGER;
BEGIN
    -- 根据用户名获取UID
    SELECT uid INTO v_uid FROM users WHERE username = p_username;

    -- 删除匹配UID的用户会话记录
    DELETE FROM user_sessions WHERE uid = v_uid;
    
    -- 更多删除语句...

    -- 删除匹配用户名的用户记录
    DELETE FROM users WHERE username = p_username;
END;
$$;

创建后查询到的函数定义：

CREATE FUNCTION "public"."delete_user_and_related_data"() RETURNS void LANGUAGE PLPGSQL
AS
$$

DECLARE
    v_uid INTEGER;
BEGIN
    -- 根据用户名获取UID
    SELECT uid INTO v_uid FROM users WHERE username = p_username;

    -- 删除匹配UID的用户会话记录
    DELETE FROM user_sessions WHERE uid = v_uid;

    -- 删除匹配UID的zofolio_user_dream_coins记录
    DELETE FROM zofolio_user_dream_coins WHERE uid = v_uid;

    -- 删除匹配UID的zofolio_user_trades记录
    DELETE FROM zofolio_user_trades WHERE uid = v_uid;

    -- 删除匹配用户名的用户记录
    DELETE FROM users WHERE username = p_username;
END;

$$

1. 修正DBGrip执行逻辑 #

DBGrip可能因语句解析问题未完整提交函数定义，需确保选中全部函数创建代码后执行，避免分段执行导致参数部分被遗漏。也可将代码保存为SQL脚本，通过\i /path/to/your_script.sql命令执行。

2. 调整函数调用方式 #

报错提示“不是存储过程”，说明调用时误用了CALL语句。PostgreSQL中调用无返回结果的函数需使用SELECT：

SELECT delete_user_and_related_data('目标用户名');

若确需使用存储过程，需用CREATE PROCEDURE语法创建，调用时用CALL，同时注意存储过程需显式管理事务（涉及多条DML操作时）。

3. 显式指定参数模式 #

修改创建语句，添加IN前缀明确参数模式，避免工具解析歧义：

CREATE OR REPLACE FUNCTION delete_user_and_related_data(IN p_username TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS
$$
DECLARE
    v_uid INTEGER;
BEGIN
    SELECT uid INTO v_uid FROM users WHERE username = p_username;
    DELETE FROM user_sessions WHERE uid = v_uid;
    DELETE FROM zofolio_user_dream_coins WHERE uid = v_uid;
    DELETE FROM zofolio_user_trades WHERE uid = v_uid;
    DELETE FROM users WHERE username = p_username;
END;
$$;

4. 验证函数创建结果 #

创建完成后，执行以下命令确认参数是否存在：

-- 查看函数基本信息
\df delete_user_and_related_data;

-- 查询系统表确认参数
SELECT proname, proargnames FROM pg_proc WHERE proname = 'delete_user_and_related_data';

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