From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Using regexp_matches in the WHERE clause |
Date: | 2012-11-27 12:26:15 |
Message-ID: | CAFj8pRAEWMKQerKw6-3mataoV8ULonHM2QghKGfBQqzZVX+i2g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello
2012/11/27 Thomas Kellerer <spam_eater(at)gmx(dot)net>:
>> > So I tried:
>> >
>> > SELECT *
>> > FROM some_table
>> > WHERE regexp_matches(somecol, 'foobar') is not null;
>> >
>> > However that resulted in: ERROR: argument of WHERE must not return a
>> set
>> >
>> > Hmm, even though an array is not a set I can partly see what the
>> problem is
>> > (although given the really cool array implementation in PostgreSQL I
>> was a bit surprised).
>> >
>> >
>> > So I though, if I convert this to an integer, it should work:
>> >
>> > SELECT *
>> > FROM some_table
>> > WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0
>> >
>> > but that still results in the same error.
>> >
>> > But array_length() clearly returns an integer, so why does it still
>> throw this error?
>> >
>> >
>> > I'm using 9.2.1
>> >
>
>
>> Sounds to me like this:
>>
>>
>> http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-parameter.html
>>
>
> Thanks, but my question is not related to the underlying problem.
>
> My question is: why I cannot use regexp_matches() in the WHERE clause, even
> when the result is clearly an integer value?
>
use a ~ operator instead
postgres=# select * from o where a ~ 'e';
a
--------
pavel
zdenek
(2 rows)
postgres=# select * from o where a ~ 'k$';
a
--------
zdenek
(1 row)
you can use regexp_matches, but it is not effective probably
postgres=# select * from o where exists (select * from
regexp_matches(o.a,'ne'));
a
--------
zdenek
(1 row)
Regards
Pavel Stehule
>
> Regards
> Thomas
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2012-11-27 12:30:59 | Re: Using regexp_matches in the WHERE clause |
Previous Message | Thomas Kellerer | 2012-11-27 12:08:05 | Re: Using regexp_matches in the WHERE clause |