Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdJad-0005x9-Lb for pgsql-sql@arkaria.postgresql.org; Tue, 27 Nov 2012 11:44:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TdJac-0002Ju-Ky for pgsql-sql@arkaria.postgresql.org; Tue, 27 Nov 2012 11:44:34 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdJab-0002Jo-Hl for pgsql-sql@postgresql.org; Tue, 27 Nov 2012 11:44:33 +0000 Received: from dub0-omc2-s24.dub0.hotmail.com ([157.55.1.163]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdJaY-0005eR-Bm for pgsql-sql@postgresql.org; Tue, 27 Nov 2012 11:44:32 +0000 Received: from DUB104-W30 ([157.55.1.136]) by dub0-omc2-s24.dub0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Tue, 27 Nov 2012 03:44:25 -0800 X-Originating-IP: [145.77.106.6] X-EIP: [plFueRpW08tL1suuJAMKfTgP88qF7NHo] X-Originating-Email: [willem_leenen@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_dd81c6c0-ec3e-473b-bb05-14ea1bfec5e9_" From: Willem Leenen To: , Subject: Re: Using regexp_matches in the WHERE clause Date: Tue, 27 Nov 2012 11:44:24 +0000 Importance: Normal In-Reply-To: References: MIME-Version: 1.0 X-OriginalArrivalTime: 27 Nov 2012 11:44:25.0111 (UTC) FILETIME=[8CAEE270:01CDCC94] X-Pg-Spam-Score: -1.3 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_dd81c6c0-ec3e-473b-bb05-14ea1bfec5e9_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =20 Sounds to me like this: =20 http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-para= meter.html =20 =20 > To: pgsql-sql@postgresql.org > From: spam_eater@gmx.net > Subject: [SQL] Using regexp_matches in the WHERE clause > Date: Mon=2C 26 Nov 2012 13:13:06 +0100 >=20 > Hi=2C >=20 > I stumbled over this question on Stackoverflow >=20 > http://stackoverflow.com/questions/13564369/postgresql-using-column-data-= as-pattern-for-regexp-match >=20 > And my initial reaction was=2C that this should be possible using regexp_= matches. >=20 > So I tried: >=20 > SELECT * > FROM some_table > WHERE regexp_matches(somecol=2C 'foobar') is not null=3B >=20 > However that resulted in: ERROR: argument of WHERE must not return a set >=20 > Hmm=2C even though an array is not a set I can partly see what the proble= m is > (although given the really cool array implementation in PostgreSQL I was = a bit surprised). >=20 >=20 > So I though=2C if I convert this to an integer=2C it should work: >=20 > SELECT * > FROM some_table > WHERE array_length(regexp_matches(somecol=2C 'foobar')=2C 1) > 0 >=20 > but that still results in the same error. >=20 > But array_length() clearly returns an integer=2C so why does it still thr= ow this error? >=20 >=20 > I'm using 9.2.1 >=20 > Regards > Thomas >=20 >=20 >=20 >=20 > --=20 > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql = --_dd81c6c0-ec3e-473b-bb05-14ea1bfec5e9_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 =3B
Sounds to me like this:
 =3B
http://joecelkothesqlapprentice.blogspot.nl/2007/12/= using-where-clause-parameter.html
 =3B

 =3B
>=3B To: pgsql-sql@postgresql.org
= >=3B From: spam_eater@gmx.net
>=3B Subject: [SQL] Using regexp_match= es in the WHERE clause
>=3B Date: Mon=2C 26 Nov 2012 13:13:06 +0100>=3B
>=3B Hi=2C
>=3B
>=3B I stumbled over this question= on Stackoverflow
>=3B
>=3B http://stackoverflow.com/questions/1= 3564369/postgresql-using-column-data-as-pattern-for-regexp-match
>=3B =
>=3B And my initial reaction was=2C that this should be possible usin= g regexp_matches.
>=3B
>=3B So I tried:
>=3B
>=3B SEL= ECT *
>=3B FROM some_table
>=3B WHERE regexp_matches(somecol=2C '= foobar') is not null=3B
>=3B
>=3B However that resulted in: ERRO= R: argument of WHERE must not return a set
>=3B
>=3B Hmm=2C even= though an array is not a set I can partly see what the problem is
>= =3B (although given the really cool array implementation in PostgreSQL I wa= s a bit surprised).
>=3B
>=3B
>=3B So I though=2C if I con= vert this to an integer=2C it should work:
>=3B
>=3B SELECT *>=3B FROM some_table
>=3B WHERE array_length(regexp_matches(somecol= =2C 'foobar')=2C 1) >=3B 0
>=3B
>=3B but that still results in= the same error.
>=3B
>=3B But array_length() clearly returns an= integer=2C so why does it still throw this error?
>=3B
>=3B >=3B I'm using 9.2.1
>=3B
>=3B Regards
>=3B Thomas
&g= t=3B
>=3B
>=3B
>=3B
>=3B --
>=3B Sent via pgs= ql-sql mailing list (pgsql-sql@postgresql.org)
>=3B To make changes to= your subscription:
>=3B http://www.postgresql.org/mailpref/pgsql-sql<= BR>
= --_dd81c6c0-ec3e-473b-bb05-14ea1bfec5e9_--