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