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
Last activity: Mar 15, 2024, 12:13 AM