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

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