Passing a date to a function gives conversion to integer error

I am trying to pass a date to a function in postgres. Here is my code:

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

I execute the function call as such:

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

I am getting the following error:

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

I can not figure out where this date = integer condition exists. In the code above I am casting the timestamp column to date.

Update: I have added a working solution as an answer. Hope it helps someone else.

Solution

This can be solved with plain SQL. No loop, no dynamic SQL, no PL/pgSQL. No window function either.

CREATE OR REPLACE FUNCTION public.path_optimization(_input_date date)
  RETURNS TABLE(area_id uuid, result_uuids text[])
  LANGUAGE sql AS
$func$
WITH RECURSIVE tree AS (
   SELECT r.areaid, r.max_uuid
        , g."centerPoint"::geometry AS vtcs
        , ST_ShortestLine(t."centerPoint"::geometry, g."centerPoint") AS segment
        , ARRAY[t.uuid, g.uuid] AS uuids
   FROM   pma_ranked r
   JOIN   potential_missed_areas t ON t."areaID" = r.areaid  -- start area
                                  AND t.uuid     = r.max_uuid
                                  AND t.status   = 'approved'
                                  AND t."createdAt"::date = _input_date  -- bad cast
   CROSS  JOIN LATERAL (
      SELECT g.uuid
           , g."centerPoint"::geometry          -- why the cast?
      FROM   potential_missed_areas g
      WHERE  g."areaID" = r.areaid
      AND    g.status = 'approved'
      AND    g."createdAt"::date = _input_date  -- bad cast
      AND    g.uuid <> t.uuid
      ORDER  BY g."centerPoint"::geometry <-> t."centerPoint"::geometry  -- bad cast (?)
      LIMIT  1
      ) g
   
   UNION ALL   
   SELECT t.areaid, t.max_uuid
        , ST_Union(t.vtcs, g."centerPoint")
        , ST_ShortestLine(t.vtcs, g."centerPoint")
        , t.uuids || g.uuid
   FROM   tree t
   CROSS  JOIN LATERAL (
      SELECT g.uuid
           , g."centerPoint"::geometry          -- why the cast?
      FROM   potential_missed_areas g
      WHERE  g."areaID" = t.areaid
      AND    g.status = 'approved'
      AND    g."createdAt"::date = _input_date  -- bad cast
      AND    g.uuid <> ALL(t.uuids)
      ORDER  BY g."centerPoint"::geometry <-> t.vtcs  -- bad cast
      LIMIT  1
      ) g
   )
SELECT DISTINCT ON (1, 2)
       t.areaid, t.uuids
FROM   tree t
ORDER  BY 1, 2, cardinality(t.uuids) DESC;
$func$;

Dynamic SQL is only required to parameterize Identifiers or code elements. Values can be passed as parameter values to plain SQL. See:

The rCTE starts with the first full segment, so we don't need WHERE segment IS NOT NULL at the end.

I replaced the final two steps with a single query using DISTINCT ON. Simpler, typically faster. See:

Select first row in each GROUP BY group?

Your ROW_NUMBER() call had no ORDER BY, but I assume you want the longest array from each path, so I added cardinality(t.uuids) DESC to the sort order.