Thread: 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.

----- Original Message -----
From: David Johnston

<SNIP>

>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).

Thanks David,

I found that if the whole expression is made a sub-select it works:

development=# SELECT CASE
development-#             WHEN (SELECT LENGTH(ARRAY_TO_STRING(
REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(#                                         , ',')
development(#                         ) = LENGTH('12-70510')
development(#                  )
development-#            THEN ARRAY_TO_STRING(
REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(#                                  , ',')
development-#             ELSE ''
development-#         END AS "12-70510"
development-# ;
 12-70510
----------

(1 row)

Cheers,
George

>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.