From: | Mike Martin <mike(at)redtux(dot)plus(dot)com> |
---|---|
To: | pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Problem with parameterised CASE UPDATE |
Date: | 2020-06-03 22:31:20 |
Message-ID: | CAOwYNKbY_qyPtQfvRjsbc=zcO5c0Y_004nZe8V19UomKSkkh3A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wed, 3 Jun 2020 at 19:16, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:
> On Wed, Jun 3, 2020 at 9:34 AM Mike Martin <mike(at)redtux(dot)plus(dot)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
>>
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Joseph | 2020-06-08 18:14:42 | Persisting Query tabs/contents over restart |
Previous Message | David G. Johnston | 2020-06-03 18:15:39 | Re: Problem with parameterised CASE UPDATE |