Re: Problem with parameterised CASE UPDATE

On Wed, Jun 3, 2020 at 9:34 AM Mike Martin <mike@redtux.plus.com> wrote:

Hi

I have the following query

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.