Question 259214 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Update - The following code works:

    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;
    input_date_param date; -- Declare a variable to hold the input date
BEGIN
    -- Iterate through the records in pma_ranked
    FOR r IN SELECT areaid, max_uuid FROM pma_ranked LOOP
        -- Set the input_date_param to the value of input_date
        input_date_param := input_date;
        
        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 -- Use $3 to refer to input_date_param
            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 -- Use $3 to refer to input_date_param
                        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_param;
        
    END LOOP;
END;
$function$;

    The function call is the same as in the question.


  2. 0

    Your format is not correct, you use %1$s.

    s formats the argument value as a simple string. A null value is
    treated as an empty string.

    Because you’re creating a SQL statement, you need quotes. And this can be done using the L:

    L quotes the argument value as an SQL literal. A null value is
    displayed as the string NULL, without quotes (equivalent to
    quote_nullable).

    Simple example:

    SELECT format('select %1$s', '2023-01-01'::date);

    Result: select 2023-01-01
    And that will be a calculation: 2023 minus 1 minus 1. An integer.

    Fix:

    SELECT format('select %1$L', '2023-01-01'::date);

    Result:
    select ‘2023-01-01’
    And that looks like a proper date, using quotes where needed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search