Re: Problem with parameterised CASE UPDATE

On Wed, 3 Jun 2020 at 19:16, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> 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.
>
> 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