PREPARE chk AS UPDATE transcodes_detail td SET sortid=CASE WHEN $1 = 6 THEN $2::numeric ELSE td.sortid END WHERE detailid=$3 execute chk (7,'1c',73)
It fails as ERROR: invalid input syntax for type numeric: "1c"
It seems to check all parameters before it tests whether parameter 1 equates to 6 (in this instance).
Is there a way round this
You can try deferring the casting of the input parameter so that the executor doesn't see it as a constant during the execution of the case expression.
Minimally tested...
create function cs (one integer, two text, def text) returns text language plpgsql immutable as $$ declare ret text; begin select (case when one = 6 then two::numeric else def::numeric end)::text into ret; return ret; end; $$;
