Re: argument of CASE/WHEN must not return a set

Sameer Kumar wrote
> On Thu, Jan 9, 2014 at 1:26 AM, George Weaver <

> gweaver@

> > wrote:
>
>> ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
>> development(# , ',')
>
>
> I guess this part of your statement will return 1,2, which is a set
>
> Can you try below:
> SELECT CASE
> WHEN LENGTH(ARRAY_TO_STRING(
> REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
> , ',')
> )
> = LENGTH('12-70510')
>
> THEN cast(ARRAY_TO_STRING(
> REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
> , ',') as varchar(100))
>
> ELSE ''
> END AS "12-70510";
>
> But anyways, I think the best way to do it is the way you have already
> figured (check the plan for both statements once you have sorted out the
> error)
>
>
> Best Regards,
> *Sameer Kumar | Database Consultant*
>
> *ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
> 069533
> M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
> www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
>
> [image: email patch]
>
> This email may contain confidential, privileged or copyright material and
> is solely for the use of the intended recipient(s).
>
>
> image002.jpg (7K)
> <http://postgresql.1045698.n5.nabble.com/attachment/5786031/0/image002.jpg>

The condition (WHEN) in a case cannot be a set. You have to make the
expression always resolve to a single row/value.

I'd suggest creating a regexp_matches_single(...) function that calls
regexp_matches(...) in a sub-select so that no matches results in null. You
then need to decide how you want to handle multiple matches. This function
will return a single text[] and so can be used in places where you want your
match to only and always return a single result (i.e. non-global behavior).

Note a recent patch was applied yesterday to resolve an ancient undiscovered
bug related to this kind of query as well. Using the above function/behavior
will let you avoid that bug as well.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/argument-of-CASE-WHEN-must-not-return-a-set-tp5785874p5786085.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.