Problem with parameterised CASE UPDATE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem with parameterised CASE UPDATE

Mike Martin-2
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

thanks in advance

Reply | Threaded
Open this post in threaded view
|

Re: Problem with parameterised CASE UPDATE

Steve Midgley-3


On Wed, Jun 3, 2020 at 9:34 AM Mike Martin <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Problem with parameterised CASE UPDATE

David G Johnston
In reply to this post by Mike Martin-2
On Wed, Jun 3, 2020 at 9:34 AM Mike Martin <[hidden email]> 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

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;
$$;

PREPARE chk ASUPDATE ex_update eu
SET
txtfld=cs($1,$2,eu.txtfld);

execute chk (7,'1c');

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Problem with parameterised CASE UPDATE

Mike Martin-2


On Wed, 3 Jun 2020 at 19:16, David G. Johnston <[hidden email]> wrote:
On Wed, Jun 3, 2020 at 9:34 AM Mike Martin <[hidden email]> 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

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;
$$;

PREPARE chk ASUPDATE ex_update eu
SET
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