将Web表单数据存入数据库的PL/pgSQL函数异常求助

我正在开发一款Web应用，下图是用户输入数据的表单：

表单包含6个文本字段，我编写的addMatchToDB函数在传入随机测试数据时可正常执行，但传入该表单文本字段的数据时却触发异常，返回错误信息。函数代码如下：

Create or replace function addMatchToDB(dataa character varying)
    returns varchar
    language plpgsql
as
$$
Declare
    innerData jsonb;
    p_res     varchar(50);
Begin
    innerData = dataa::jsonb;
    INSERT INTO mts_stolni_partija(id, rezultat, datum, id_igrac1, id_igrac2, id_igrac3, id_igrac4)
    VALUES (nextval('mts_sekvencaStolni'), innerData ->> 'FORM_INPUT_STOLNI_REZ',
            innerData ->> 'FORM_INPUT_STOLNI_UTDATUM',
            (SELECT ID FROM mts_stolni_igrac WHERE ime = innerData ->> 'FORM_INPUT_STOLNI_IGRAC1'),
            (SELECT ID FROM mts_stolni_igrac WHERE ime = innerData ->> 'FORM_INPUT_STOLNI_IGRAC2'),
            (SELECT ID FROM mts_stolni_igrac WHERE ime = innerData ->> 'FORM_INPUT_STOLNI_IGRAC3'),
            (SELECT ID FROM mts_stolni_igrac WHERE ime = innerData ->> 'FORM_INPUT_STOLNI_IGRAC4'));
    p_res := '1|successful!';
    return p_res;
EXCEPTION
    WHEN OTHERS THEN
        p_res := '-1|Error!';
        RETURN p_res;
End;
$$;

1. JSON键名不匹配 #

前端表单提交的字段名和函数中使用的FORM_INPUT_STOLNI_REZ等键名可能存在大小写、拼写或格式差异（比如前端用驼峰命名，函数用下划线）。

• 解决：打印前端提交的完整JSON数据，逐一核对键名是否完全一致。

2. 数据类型不兼容 #

• 日期格式错误：如果datum字段是日期类型，前端传入的日期字符串格式不符合PostgreSQL要求（比如MM/DD/YYYY而非YYYY-MM-DD），会导致插入失败。
  • 解决：要么前端统一传YYYY-MM-DD格式，要么在函数中转换格式：

    TO_DATE(innerData ->> 'FORM_INPUT_STOLNI_UTDATUM', 'MM/DD/YYYY')
    

• 玩家ID查询返回NULL：如果表单输入的玩家名称在mts_stolni_igrac表中不存在，子查询会返回NULL，若id_igrac1等字段不允许NULL则触发异常。
  • 解决：前端校验玩家名称必须存在，或者在函数中用COALESCE给默认值（如果业务允许）：

    COALESCE((SELECT ID FROM mts_stolni_igrac WHERE ime = innerData ->> 'FORM_INPUT_STOLNI_IGRAC1'), 0)
    

3. 子查询返回多行结果 #

如果mts_stolni_igrac表中存在多个同名玩家，子查询会返回多行，导致INSERT报错。

• 解决：给子查询添加LIMIT 1确保只返回一行：

  (SELECT ID FROM mts_stolni_igrac WHERE ime = innerData ->> 'FORM_INPUT_STOLNI_IGRAC1' LIMIT 1)
  

4. JSON格式非法 #

前端提交的JSON存在语法错误（比如引号不闭合、逗号错误），导致dataa::jsonb转换失败。

• 解决：前端提交前校验JSON合法性，或者在函数中捕获转换错误：

  Begin
      BEGIN
          innerData = dataa::jsonb;
      EXCEPTION
          WHEN OTHERS THEN
              p_res := '-1|Invalid JSON format!';
              RETURN p_res;
      END;
      -- 后续INSERT逻辑
  

5. 优化错误排查 #

当前异常块只返回通用错误信息，无法定位具体问题。修改函数返回详细错误：

EXCEPTION
    WHEN OTHERS THEN
        p_res := '-1|Error: ' || SQLERRM;
        RETURN p_res;

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