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