To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Using regexp_matches in the WHERE clause
Date: Tue, 27 Nov 2012 13:08:05 +0100
Lines: 39
Message-ID: <k92ae9$di0$1@ger.gmane.org>
References: <k8vmbm$1fg$1@ger.gmane.org>
	<DUB104-W30ACC9F07483AD6B500D698F5E0@phx.gbl>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: <DUB104-W30ACC9F07483AD6B500D698F5E0@phx.gbl>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


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