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
>
> This would be pretty hacky and non-performant, but maybe you could use
dynamic sql for your cast statement to prevent it from evaluating before
the $1 evaluation? https://www.postgresql.org/docs/current/ecpg-dynamic.html

Somehow it seems like you need the interpreter to execute evaluation logic
before casting the variable types, which seems hard (to me).

Steve