Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux