Re: Problem with parameterised CASE UPDATE

On Wed, Jun 3, 2020 at 9:34 AM Mike Martin <mike(at)redtux(dot)plus(dot)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.