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; $$;
PREPARE chk ASUPDATE ex_update eu
SET txtfld=cs($1,$2,eu.txtfld);
execute chk (7,'1c');
David J.
Thanks for suggestions, in the end I rewrote the query (which was a part of the final query) as an upsert ie
INSERT INTO transcodes_detail SELECT $1,$2,$3,$4,$5,$6,$7 ON CONFLICT ON CONSTRAINT keyid DO UPDATE SET sortid=EXCLUDED.sortid, optname=EXCLUDED.optname,optargs=EXCLUDED.optargs,optsep=EXCLUDED.optsep,prepost=EXCLUDED.prepost
