PostgreSQL函数传日期报错：date = integer运算符不存在

问题场景 #

我编写了一个接收日期参数的PostgreSQL函数path_optimization，用于处理路径优化逻辑。调用函数时出现以下错误：

SQL Error [42883]: ERROR: operator does not exist: date = integer
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Where: PL/pgSQL function path_optimization(date) line 10 at RETURN QUERY

原函数代码：

CREATE OR REPLACE FUNCTION public.path_optimization(input_date date)
 RETURNS TABLE(area_id_ uuid, result_uuids text[])
 LANGUAGE plpgsql
AS $function$
DECLARE
    
r RECORD;
BEGIN
    -- Iterate through the records in pma_ranked
    FOR r IN SELECT areaid, max_uuid FROM pma_ranked LOOP
        --RAISE NOTICE 'Processing areaid: %, max_uuid: %', r.areaid, r.max_uuid;

        RETURN QUERY
        execute format (
        '
        WITH RECURSIVE
        tree AS (
            SELECT
                g."centerPoint"::GEOMETRY AS vtcs,
                NULL::GEOMETRY AS segment,
                ARRAY[g.uuid] AS uuids
            FROM
                potential_missed_areas AS g
            WHERE
                g."areaID" = $1
                AND g.uuid = $2
                AND g.status = ''approved''
                AND g."createdAt"::date = %1$s
            UNION ALL   
            SELECT
                ST_Union(t.vtcs, v."centerPoint"),
                ST_ShortestLine(t.vtcs, v."centerPoint"),
                t.uuids || v.uuid  
            FROM
                tree AS t
                CROSS JOIN LATERAL (
                    SELECT
                        g.uuid,
                        g."centerPoint"
                    FROM
                        potential_missed_areas AS g
                    WHERE
                        g."areaID" = $1
                        AND g.status = ''approved''
                        AND g."createdAt"::date = %1$s
                        AND NOT g.uuid = ANY(t.uuids)
                    ORDER BY
                        t.vtcs <-> g."centerPoint"
                    LIMIT
                        1
                ) AS v
            ),
            NumberedRows AS (
                SELECT 
                    $1,uuids,
                    ROW_NUMBER() OVER () AS RowNum
                FROM tree
                WHERE segment IS NOT NULL
            )
            SELECT 
            $1,uuids
            FROM NumberedRows
            WHERE RowNum = (SELECT MAX(RowNum) FROM NumberedRows);
        '
        ,input_date)
        
        USING r.areaid, r.max_uuid;

    END LOOP;
END;
$function$
;

调用语句：

select * from public.path_optimization('2023-10-04'::date)

错误原因 #

问题出在使用format函数拼接SQL时，直接将input_date（date类型）插入到SQL文本中。PostgreSQL的format函数会把date类型转换为无引号的数字格式（例如2023-10-04会被转成20231004），导致生成的SQL中出现g."createdAt"::date = 20231004这样的语句。数据库会将20231004识别为整数，自然无法与date类型的字段进行比较，触发类型不匹配错误。

解决方案 #

避免用format拼接参数，改用占位符+USING子句的方式传入日期参数，让PostgreSQL自动处理类型转换，确保参数类型正确。修改后的函数代码如下：

CREATE OR REPLACE FUNCTION public.path_optimization(input_date date)
 RETURNS TABLE(area_id_ uuid, result_uuids text[])
 LANGUAGE plpgsql
AS $function$
DECLARE
    
r RECORD;
BEGIN
    -- Iterate through the records in pma_ranked
    FOR r IN SELECT areaid, max_uuid FROM pma_ranked LOOP
        --RAISE NOTICE 'Processing areaid: %, max_uuid: %', r.areaid, r.max_uuid;

        RETURN QUERY
        execute '
        WITH RECURSIVE
        tree AS (
            SELECT
                g."centerPoint"::GEOMETRY AS vtcs,
                NULL::GEOMETRY AS segment,
                ARRAY[g.uuid] AS uuids
            FROM
                potential_missed_areas AS g
            WHERE
                g."areaID" = $1
                AND g.uuid = $2
                AND g.status = ''approved''
                AND g."createdAt"::date = $3
            UNION ALL   
            SELECT
                ST_Union(t.vtcs, v."centerPoint"),
                ST_ShortestLine(t.vtcs, v."centerPoint"),
                t.uuids || v.uuid  
            FROM
                tree AS t
                CROSS JOIN LATERAL (
                    SELECT
                        g.uuid,
                        g."centerPoint"
                    FROM
                        potential_missed_areas AS g
                    WHERE
                        g."areaID" = $1
                        AND g.status = ''approved''
                        AND g."createdAt"::date = $3
                        AND NOT g.uuid = ANY(t.uuids)
                    ORDER BY
                        t.vtcs <-> g."centerPoint"
                    LIMIT
                        1
                ) AS v
            ),
            NumberedRows AS (
                SELECT 
                    $1,uuids,
                    ROW_NUMBER() OVER () AS RowNum
                FROM tree
                WHERE segment IS NOT NULL
            )
            SELECT 
            $1,uuids
            FROM NumberedRows
            WHERE RowNum = (SELECT MAX(RowNum) FROM NumberedRows);
        '
        USING r.areaid, r.max_uuid, input_date;

    END LOOP;
END;
$function$
;

修改说明 #

• 移除format函数，使用静态SQL模板
• 将原SQL中所有的%1$s替换为$3（$1、$2已被r.areaid、r.max_uuid占用）
• 在USING子句末尾添加input_date，由PostgreSQL自动处理参数类型匹配

修改后，原调用语句可以正常执行：

select * from public.path_optimization('2023-10-04'::date)

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