Thread: Using regexp_matches in the WHERE clause

Hi,

I stumbled over this question on Stackoverflow
   http://stackoverflow.com/questions/13564369/postgresql-using-column-data-as-pattern-for-regexp-match

And my initial reaction was, that this should be possible using regexp_matches.

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

Regards
Thomas

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

Regards
Thomas

Hello

2012/11/27 Thomas Kellerer <spam_eater@gmx.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
--------pavelzdenek
(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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

Pavel Stehule, 27.11.2012 13:26:
>> 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
>

So that means, regexp_matches cannot be used as an expression in the WHERE clause?

Regards
Thomas

2012/11/27 Thomas Kellerer <spam_eater@gmx.net>:
> Pavel Stehule, 27.11.2012 13:26:
>
>>> 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
>>
>
> So that means, regexp_matches cannot be used as an expression in the WHERE

should not be used - it is designed to return matched values, no for
returning true or false,

you can do some obscure
postgres=# select * from o where array(select
(regexp_matches(a,'ne'))[1]) <> '{}'::text[];  a
--------zdenek
(1 row)

but it is not recommended.

Regards

Pavel

> clause?
>
>
> Regards
> Thomas
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

On Nov 26, 2012, at 7:13, Thomas Kellerer <spam_eater@gmx.net> wrote:

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

regex_matches returns a set because you can supply the "g" option to capture all matches and each separate match
returnsits own record.  Even though only one record is ever returned without the "g" option the function itself is the
sameand still is defined to return a set. 

David J.

I noticed that regexp_matches already returns the rows which matches the
regular expression

now when I make a full table select query


but if I make a search with regexp_matches, it only returns rows that
matches regular expression


on pgadmin the column type is shown as text[] thus I also do not understand
why array_length on where condition does not work for this.



But maybe as it was pointed out, the return type is setof text[], that the
result could have been like this (one row of data may result in multiple
rows)


If you have a regular expression that may end in one or more elements in the
text[] then you may use inner query


ps: last query is pointless as it will return 2 elements for each row (that
matches the regular expression), but there may be a regular expression that
may return one or more elements for each row



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Using-regexp-matches-in-the-WHERE-clause-tp5733684p5768923.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

spulatkan wrote
> so following is enough to get the rows that matches regular expression

> 

This is bad form even if it works.  If the only point of the expression is
to filter rows it should appear in the WHERE clause.  The fact that
regexp_matches(...) behaves in this way at all is, IMO, a flaw of the
implementation.


> on pgadmin the column type is shown as text[] thus I also do not
> understand why array_length on where condition does not work for this.

> 

This works because the array_length formula is applied once to each "row" of
the returned set.  

As mentioned before it makes absolutely no sense to evaluate a set-returning
function within the WHERE clause and so attempting to do so causes a fatal
exception.  For my usage I've simply written a wrapper function that
implements the same basic API as regexp_matches but that returns a scalar
"text[]" instead of a "setof text[]".  It makes coding these kinds of
queries easier if you know/understand the fact that your matching will never
cause more than 1 row to be returned.  If zero rows are returned I return an
empty array and the normal 1-row case returns the matching array.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Using-regexp-matches-in-the-WHERE-clause-tp5733684p5768926.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.