Bug reference: 15109 Logged by: Bernhard Reutner-Fischer Email address: rep.dot.nop@gmail.com PostgreSQL version: 10.3 Operating system: linux Description:
Hi!
wishlist item: ---8<--- CREATE OR REPLACE FUNCTION xxx(flag boolean) RETURNS JSONB LANGUAGE plpgsql AS $$ DECLARE row record; BEGIN IF flag = true THEN SELECT 1::bigint AS i INTO row; ELSE SELECT 1::text AS i INTO row; END IF; RETURN JSONB_BUILD_OBJECT('foo', row.i); END $$;
psql:xxx.sql:19: ERROR: type of parameter 4 (bigint) does not match that when preparing the plan (text) CONTEXT: PL/pgSQL function xxx(boolean) line 12 at RETURN
Would be nice to give more context what "parameter 4" actually is, i.e. also print the name or underline the exact line the error occurs in or the like (and how comes we talk about line 12 and not 14?):
Once you understand the error you will see it is indeed on line 12, count them including the line with the starting $$
The error itself comes up not because of the "SELECT 1" but because of "row.i" being passed into jsonb_build_object('foo', $#) - in one pass row.i is text then the next invocation of the now-cached function-plan it is a bigint. That row.i changes types is allowed by design - its this specific usage of row.i that is problematic.
Parameters don't have names, just numbers, so finding a name to pass back is definitely non-trivial.
mockup: psql:xxx.sql:19: ERROR: type of parameter 4 (1::bigint AS i) does not match that when preparing the plan (1::text AS i)
As noted above, since that isn't where the actual error is occurring a suggestion like this is even not helpful since you not only want a better error message but you want something that is not inherently an error (i.e., polymorphic record types) to become one just so push the error message further up the procedure. That isn't acceptable. And tracing from the statement retrieving "row.i" back to those two statements that set row is likely impossible.
In short - this isn't a bug report so this isn't the right list for the discussion (that would be -general), and while the frustration is real the proposed alternatives do not seem realistic.