PostgreSQL带日期参数的函数执行报错，请求修正方案

错误原因是动态SQL字符串无法直接访问PL/pgSQL的变量input_date，它会被解析为数据库列名，导致"column does not exist"错误。你尝试用占位符替换的思路是对的，但需要确保所有input_date都替换为对应占位符，同时在USING子句中传入参数。

具体修改点： #

• 将动态SQL中两处g."createdAt" = input_date替换为g."createdAt" = $3
• 在USING r.areaid, r.max_uuid;末尾添加, input_date，让第三个占位符$3对应函数的输入参数

修正后的完整函数代码： #

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" = $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" = $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$
;

验证调用： #

执行原调用语句即可正常运行：

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

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