Home > mailing lists

Problem with parameterised CASE UPDATE - Mailing list pgsql-sql

From	Mike Martin
Subject	Problem with parameterised CASE UPDATE
Date	June 3, 2020 19:33:23
Msg-id	CAOwYNKaRNDMSCE1w6CxnyqDXzqoqTqjdmqne8wFCkhX2D=YJEw@mail.gmail.com Whole thread Raw
Responses	Re: Problem with parameterised CASE UPDATE (Steve Midgley <science@misuse.org>) Re: Problem with parameterised CASE UPDATE ("David G. Johnston" <david.g.johnston@gmail.com>)
List	pgsql-sql

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

From: Mark Bannister
Date: 03 June 2020, 16:52:40
Subject: Recursive CTE with a function

From: Steve Midgley
Date: 03 June 2020, 21:02:27
Subject: Re: Problem with parameterised CASE UPDATE