Re: Problem with parameterised CASE UPDATE
| От | David G. Johnston |
|---|---|
| Тема | Re: Problem with parameterised CASE UPDATE |
| Дата | |
| Msg-id | CAKFQuwbhOUgnOpJ0qgYpY1Zf4RCWn1V+eCv2tPqGfj=4i1OFCg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Problem with parameterised CASE UPDATE (Mike Martin <mike@redtux.plus.com>) |
| Ответы |
Re: Problem with parameterised CASE UPDATE
|
| Список | 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
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;
$$;
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
SETtxtfld=cs($1,$2,eu.txtfld);
execute chk (7,'1c');
David J.
В списке pgsql-sql по дате отправления: