Thread: Problem with parameterised CASE UPDATE
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)
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
thanks in advance
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
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.
On Wed, 3 Jun 2020 at 19:16, David G. Johnston <david.g.johnston@gmail.com> wrote:
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 thisYou 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;
$$;PREPARE chk ASUPDATE ex_update euSET
txtfld=cs($1,$2,eu.txtfld);
execute chk (7,'1c');David J.
Thanks for suggestions, in the end I rewrote the query (which was a part of the final query) as an upsert ie
INSERT INTO transcodes_detail
SELECT $1,$2,$3,$4,$5,$6,$7
ON CONFLICT ON CONSTRAINT keyid DO UPDATE SET
sortid=EXCLUDED.sortid, optname=EXCLUDED.optname,optargs=EXCLUDED.optargs,optsep=EXCLUDED.optsep,prepost=EXCLUDED.prepost
SELECT $1,$2,$3,$4,$5,$6,$7
ON CONFLICT ON CONSTRAINT keyid DO UPDATE SET
sortid=EXCLUDED.sortid, optname=EXCLUDED.optname,optargs=EXCLUDED.optargs,optsep=EXCLUDED.optsep,prepost=EXCLUDED.prepost