Re: Problem with parameterised CASE UPDATE

Поиск
Список
Период
Сортировка
От Steve Midgley
Тема Re: Problem with parameterised CASE UPDATE
Дата
Msg-id CAJexoSJq1riO3KiBADNT16QPgmbRbXQMOornuW3M119-wDqb4w@mail.gmail.com
обсуждение исходный текст
Ответ на Problem with parameterised CASE UPDATE  (Mike Martin <mike@redtux.plus.com>)
Список pgsql-sql
Дерево обсуждения


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 по дате отправления:

Предыдущее
От: Mike Martin
Дата:
Сообщение: Problem with parameterised CASE UPDATE
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Problem with parameterised CASE UPDATE