带逗号的Decimal值传入PostgreSQL函数的报错问题

错误信息 #

ERROR: cannot cast type record to numeric 
LINE 1: SELECT controldubbel2('2023-01-05'::date,'DL453453530005300114'::varchar,'HALTEST'::varchar,'Naam: John Doe'::varchar,10,00::numeric, 23686,98::numeric, 'GT'::varchar)
SQL state: 42846

函数定义 #

CREATE OR REPLACE FUNCTION public.controldubbel2(_datum date, _naamtegen character varying, _tegenrekening character varying, _omschrijving character varying, _bedrag numeric, _saldo numeric, _code character varying)
 RETURNS TABLE(bestaat boolean)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT EXISTS (SELECT 1 FROM "xxxxxxxxxxxxxxx" WHERE "DATUM"=_datum
    AND "TEGENREKENING_IBAN_BBAN"=_tegenrekening AND
    "NAAM_TEGENPARTIJ"=_naamtegen AND "Omschrijving_1"=_omschrijving AND
    "BEDRAG"=_bedrag AND "saldo"=_saldo AND "CODE"=_code) as bestaat
LIMIT 1;
END
$function$

Python执行代码 #

cursor.execute(f"SELECT controldubbel2('{datum2}', '{tegenrekening}', '{naamtegen}', '{omschrijving1}', '{bedrag}', '{saldo}', '{code}')")

核心问题 #

1. 参数数量不匹配：函数定义仅接受7个参数，但调用时带逗号的数值（如10,00）被PostgreSQL识别为参数分隔符，拆成两个独立参数，导致实际传入参数数量远超函数预期，触发类型转换错误。
2. 小数分隔符冲突：PostgreSQL默认以.作为小数分隔符，而数据源使用,，直接拼接字符串会引发解析混乱。

解决方法 #

方法1：使用Python参数化查询（推荐） #

参数化查询既能避免SQL注入风险，又能自动处理类型转换，彻底解决分隔符问题：

# 若bedrag/saldo是带逗号的字符串，先转换为数值
bedrag_num = float(bedrag.replace(',', '.'))
saldo_num = float(saldo.replace(',', '.'))

# 用占位符传递参数（psycopg2用%s，其他驱动可能用?）
cursor.execute(
    "SELECT controldubbel2(%s, %s, %s, %s, %s, %s, %s)",
    (datum2, tegenrekening, naamtegen, omschrijving1, bedrag_num, saldo_num, code)
)

方法2：手动处理分隔符后拼接SQL（不推荐，存在SQL注入风险） #

如果必须用字符串拼接，先将数值中的逗号替换为点，再转换为numeric：

bedrag_formatted = bedrag.replace(',', '.')
saldo_formatted = saldo.replace(',', '.')

cursor.execute(f"""
SELECT controldubbel2(
    '{datum2}', 
    '{tegenrekening}', 
    '{naamtegen}', 
    '{omschrijving1}', 
    {bedrag_formatted}::numeric, 
    {saldo_formatted}::numeric, 
    '{code}'
)
""")

或者用PostgreSQL的TO_NUMBER函数直接处理带逗号的字符串：

cursor.execute(f"""
SELECT controldubbel2(
    '{datum2}', 
    '{tegenrekening}', 
    '{naamtegen}', 
    '{omschrijving1}', 
    TO_NUMBER('{bedrag}', '999999999D99', 'NLS_NUMERIC_CHARACTERS='',.'''), 
    TO_NUMBER('{saldo}', '999999999D99', 'NLS_NUMERIC_CHARACTERS='',.'''), 
    '{code}'
)
""")

方法3：修改数据库区域设置（全局生效，谨慎操作） #

如果整个数据库环境都使用逗号作为小数分隔符，可以修改PostgreSQL的lc_numeric参数为对应区域（如nl_NL.UTF-8），但此设置会影响所有数据库操作，需评估后执行。

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