Postgres Pro
Downloads
Home   >   mailing lists

Re: Problem with parameterised CASE UPDATE - Mailing list pgsql-sql

From Steve Midgley
Subject Re: Problem with parameterised CASE UPDATE
Date
Msg-id CAJexoSJq1riO3KiBADNT16QPgmbRbXQMOornuW3M119-wDqb4w@mail.gmail.com
Whole thread Raw
In response to Problem with parameterised CASE UPDATE  (Mike Martin <mike@redtux.plus.com>)
List pgsql-sql
Tree view


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

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

pgsql-sql by date:

Previous
From: Mike Martin
Date:
Subject: Problem with parameterised CASE UPDATE
Next
From: "David G. Johnston"
Date:
Subject: Re: Problem with parameterised CASE UPDATE