From: | "George Weaver" <gweaver(at)shaw(dot)ca> |
---|---|
To: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: argument of CASE/WHEN must not return a set |
Date: | 2014-01-09 16:02:35 |
Message-ID: | 12DBABC1C169408BA1AFA611DAFE11DE@D420 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
----- 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-01-09 16:25:36 | Re: getting domain information from query results |
Previous Message | David Johnston | 2014-01-09 15:30:58 | Re: argument of CASE/WHEN must not return a set |