2

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.

4
  • Also note that your function returns different data types names, as type varchar, not actually different data types, which it couldn't (unless you involve polymorphism, different topic). Commented Mar 14 at 1:46
  • Yes, it is just for testing purposes. Commented Mar 14 at 12:42
  • Also, this inline comment is misleading: -- Evaluating the random condition and returning different strings. It's different data types (not strings), which is the problem. Commented Mar 15 at 0:16
  • 1
    Technically your session could run DISCARD plans; between function calls to work around plpgsql caching. But from a design perspective, your idea is probably to do some kind of polymorphism in tuples, which is often somewhere between hard to impossible in SQL. Commented Mar 15 at 16:59

1 Answer 1

2

It doesn't matter the data type, the ELSE block will always fail.

Actually, the assignment in the ELSE block works fine. The error message

CONTEXT:  PL/pgSQL function test(integer) line 15 at RETURN

refers to:

RETURN pg_typeof(_new_record.id)::varchar;

A variable of type (anonymous) record can contain any row type, you can even repeatedly assign incompatible row types to the same variable within the same code block.
But PL/pgSQL functions (unlike SQL functions) treat all nested SQL statements like prepared statements. Even record types have to be the same (or matching) in repeated calls.

So the first call of your function always succeeds. But subsequent calls in the same session have to work with the same prepared statement.

If the problem is only triggered by a particular query plan, we can avoid it using dynamic SQL with EXECUTE, where the plan is never saved & reused. But tests indicate that the prepared statement itself does not allow the data type to change.

Possible workaround (as hinted by Laurenz): a separate assignment for each branch. Each statement looks the same on the surface, but is prepared differently due to the different data type:

CREATE OR REPLACE FUNCTION public.test(lever int, OUT result text)
  LANGUAGE plpgsql PARALLEL SAFE AS
$func$
DECLARE
   _new_record record;
BEGIN
   -- return different data types  (!) depending on input
   IF lever = 0 THEN
     SELECT random() AS id
     INTO _new_record;
   ELSE
     SELECT 10 AS id
     INTO _new_record;
   END IF;

   -- do stuff

   IF lever = 0 THEN  -- mirror cases at the top!
      result := pg_typeof(_new_record.id);  -- prepared differently!
   ELSE
      result := pg_typeof(_new_record.id);  -- prepared differently!
   END IF;
END
$func$;

fiddle

Related:

For what it's worth, the example you show can be reduced to this simple SQL function (which does not run into the same problem):

CREATE OR REPLACE FUNCTION public.test(lever int)
  RETURNS text
  LANGUAGE sql PARALLEL SAFE AS
$func$
-- Evaluate random condition and return different strings
SELECT CASE lever
          WHEN 0 THEN pg_typeof(uuid_generate_anura())
          ELSE pg_typeof(10)
       END;
$func$;

But you already hinted that your case is more involved.

10
  • Would it work to set plan_cache_mode = force_generic_plan on the function? Commented Mar 14 at 8:19
  • @LaurenzAlbe: I would have expected that it should. But a quick test shows it does not. Which is odd. Re-running my original tests, it turns out I had false positives and my "solution" does not work. Actually, I am not sure, why. Commented Mar 14 at 8:54
  • The parameter data type is part of the prepared statement, I'd say. Commented Mar 14 at 9:10
  • Just I wasn't expecting that the expression in the USING clause of EXECUTE would also qualify for a cached plan. Thinking about it now, that might just be the case. Then again, I don't understand why SET plan_cache_mode = force_generic_plan wouldn't help, either. Commented Mar 14 at 9:14
  • 1
    I think the solution is to have a RETURN statement in each branch. They may look the same, but are different. Commented Mar 14 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.