Sample Header Ad - 728x90

Error: type of parameter n (X) does not match that when preparing the plan (Y)

2 votes
1 answer
555 views
I'm encountering an issue with a PL/pgSQL function where it returns different data types based on a condition, and it's resulting in a type mismatch error. Here's a simplified version of the function:
CREATE OR REPLACE FUNCTION public.test(lever int)
    RETURNS VARCHAR
    LANGUAGE plpgsql
AS $function$
DECLARE
    _new_record RECORD;
BEGIN
    -- Evaluating the random condition and returning different strings
    IF (lever = 0) THEN
        SELECT * FROM
        (VALUES(uuid_generate_anura()))x(id)
        INTO _new_record;
    ELSE
        SELECT * FROM
        (VALUES(10))x(id)
        INTO _new_record;
    END IF;
    RETURN pg_typeof(_new_record.id)::varchar;
END;
$function$;
Note that this is a very simplified version of my complete function. I'm interested in understanding why and how I can work around it. When calling this function with lever = 0, it correctly returns the text uuid. However, when calling it with lever = 1 to force the ELSE statement to execute, it throws an error:
postgres=# select test(0);
 test 
------
 uuid
(1 row)
postgres=# select test(1);
ERROR:  type of parameter 4 (integer) does not match that when preparing the plan (uuid)
CONTEXT:  PL/pgSQL function test(integer) line 15 at RETURN
It doesn't matter the data type, the ELSE block will always fail.
Asked by Roberto Iglesias (47 rep)
Mar 13, 2024, 08:03 PM
Last activity: Mar 15, 2024, 12:13 AM