From kclau60@netvigator.com Fri Mar  24 15:26:14 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id B44CA9DCA31
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 11:26:14 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 42871-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 11:26:19 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 550789DC890
	for <pgsql-performance@postgresql.org>;
	Fri, 24 Mar 2006 11:26:11 -0400 (AST)
Received: from obav06.netvigator.com (obav06.netvigator.com [218.102.62.132])
	by svr4.postgresql.org (Postfix) with ESMTP id 998BB5AF975
	for <pgsql-performance@postgresql.org>;
	Fri, 24 Mar 2006 15:26:16 +0000 (GMT)
Received: from obav06.netvigator.com (obav01.netvigator.com [127.0.0.1])
	by obav06.netvigator.com (Postfix) with SMTP id 87AF3E0A51
	for <pgsql-performance@postgresql.org>;
	Fri, 24 Mar 2006 15:26:07 +0000 (GMT)
Received: from n2.netvigator.com ([219.78.154.228])
	by wmail05dat.netvigator.com
	(InterMail vM.6.01.05.02 201-2131-123-102-20050715) with ESMTP id
	<20060324152607.DMSG8031.wmail05dat.netvigator.com@n2.netvigator.com>
	for <pgsql-performance@postgresql.org>;
	Fri, 24 Mar 2006 23:26:07 +0800
Received: from n2.netvigator.com ([127.0.0.1])
	by [127.0.0.1] with ESMTP (SpamPal v1.594)
	sender <kclau60@netvigator.com>; 24 Mar 2006 23:26:07 +????
Message-Id: <6.2.1.2.0.20060324225743.04d49ac0@localhost>
X-Mailer: QUALCOMM Windows Eudora Version 6.2.1.2
Date: Fri, 24 Mar 2006 23:25:00 +0800
To: pgsql-performance@postgresql.org
From: K C Lau <kclau60@netvigator.com>
Subject: limitation using LIKE on ANY(array)
In-Reply-To: <20060324133252.GA22688@winnie.fuhr.org>
References: <200603170625.k2H6PJ926955@pillette.com>
	<1143112188.2810.27.camel@theo-laptop.int.coza.net.za>
	<20060323132448.GB4881@surnet.cl> <4423E90E.2090607@rentalia.com>
	<20060324125245.GB90527@pervasive.com>
	<4423EDA9.3090502@rentalia.com>
	<20060324130619.GF90527@pervasive.com>
	<20060324133252.GA22688@winnie.fuhr.org>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=====================_833631218==.ALT"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.441 required=5 tests=[DNS_FROM_RFC_POST=1.44,
	HTML_MESSAGE=0.001]
X-Spam-Score: 1.441
X-Spam-Level: *
X-Archive-Number: 200603/500
X-Sequence-Number: 17937

--=====================_833631218==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed


With 8.1.3, I get an error when trying to do this on a Text[] column :
.. WHERE ANY(array) LIKE 'xx%'

Indeed, I get rejected even with:
.. WHERE ANY(array) = 'xx'

In both cases, the error is: ERROR:  syntax error at or near "any" ...

It would only work as documented in the manual (8.10.5):
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

It appears that this restriction is still in place in 8.2:
>http://developer.postgresql.org/docs/postgres/arrays.html

Is that the case?

Thanks in advance,
KC. 
--=====================_833631218==.ALT
Content-Type: text/html; charset="us-ascii"

<html>
<body>
<br>
<pre>With 8.1.3, I get an error when trying to do this on a Text[] column
:
.. WHERE ANY(array) LIKE 'xx%'

Indeed, I get rejected even with:
.. WHERE ANY(array) = 'xx'

In both cases, the error is: ERROR:&nbsp; syntax error at or near
&quot;any&quot; ... 

It would only work as documented in the manual (8.10.5):
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

</pre>It appears that this restriction is still in place in
8.2:<blockquote type=cite class=cite cite="">
<a href="http://developer.postgresql.org/docs/postgres/arrays.html" eudora="autourl">
http://developer.postgresql.org/docs/postgres/arrays.html</a></blockquote>
<br>
Is that the case?<br><br>
Thanks in advance,<br>
KC.</body>
</html>

--=====================_833631218==.ALT--




From tgl@sss.pgh.pa.us Fri Mar  24 16:26:03 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 1941F9DC841
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 12:26:03 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 68325-08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 12:26:01 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id D94F49DC84D
	for <pgsql-performance@postgresql.org>;
	Fri, 24 Mar 2006 12:26:00 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k2OGPtIs005276;
	Fri, 24 Mar 2006 11:25:55 -0500 (EST)
To: K C Lau <kclau60@netvigator.com>
cc: pgsql-performance@postgresql.org
Subject: Re: limitation using LIKE on ANY(array) 
In-reply-to: <6.2.1.2.0.20060324225743.04d49ac0@localhost> 
References: <200603170625.k2H6PJ926955@pillette.com>
	<1143112188.2810.27.camel@theo-laptop.int.coza.net.za>
	<20060323132448.GB4881@surnet.cl> <4423E90E.2090607@rentalia.com>
	<20060324125245.GB90527@pervasive.com>
	<4423EDA9.3090502@rentalia.com>
	<20060324130619.GF90527@pervasive.com>
	<20060324133252.GA22688@winnie.fuhr.org>
	<6.2.1.2.0.20060324225743.04d49ac0@localhost>
Comments: In-reply-to K C Lau <kclau60@netvigator.com>
	message dated "Fri, 24 Mar 2006 23:25:00 +0800"
Date: Fri, 24 Mar 2006 11:25:55 -0500
Message-ID: <5275.1143217555@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.252 required=5 tests=[AWL=-0.024,
	MAILTO_TO_SPAM_ADDR=0.276]
X-Spam-Score: 0.252
X-Spam-Level: 
X-Archive-Number: 200603/503
X-Sequence-Number: 17940

K C Lau <kclau60@netvigator.com> writes:
> Indeed, I get rejected even with:
> .. WHERE ANY(array) = 'xx'

> It would only work as documented in the manual (8.10.5):
> SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

That's not changing any time soon; the SQL spec defines only the second
syntax for ANY, and I believe there would be syntactic ambiguity if we
tried to allow the other.

> With 8.1.3, I get an error when trying to do this on a Text[] column :
> .. WHERE ANY(array) LIKE 'xx%'

If you're really intent on doing that, make an operator for "reverse
LIKE" and use it with the ANY on the right-hand side.

regression=# create function rlike(text,text) returns bool as
regression-# 'select $2 like $1' language sql strict immutable;
CREATE FUNCTION
regression=# create operator ~~~ (procedure = rlike, leftarg = text,
regression(# rightarg = text, commutator = ~~);
CREATE OPERATOR
regression=# select 'xx%' ~~~ any(array['aaa','bbb']);
 ?column?
----------
 f
(1 row)

regression=# select 'xx%' ~~~ any(array['aaa','xxb']);
 ?column?
----------
 t
(1 row)

regression=#

			regards, tom lane



From kclau60@netvigator.com Sat Mar  25 01:50:03 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 4BAD79DC818
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 21:50:03 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 00510-08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 24 Mar 2006 21:50:06 -0400 (AST)
X-Greylist: delayed 10:23:51.823294 by SQLgrey-
Received: from obav02.netvigator.com (obav02.netvigator.com [218.102.62.130])
	by postgresql.org (Postfix) with ESMTP id D845A9DC806
	for <pgsql-performance@postgresql.org>;
	Fri, 24 Mar 2006 21:50:00 -0400 (AST)
Received: from obav02.netvigator.com (obav02.netvigator.com [127.0.0.1])
	by obav02.netvigator.com (Postfix) with SMTP id 663021B22D0;
	Sat, 25 Mar 2006 01:50:01 +0000 (GMT)
Received: from n2.netvigator.com ([219.78.154.228])
	by imsm058dat.netvigator.com
	(InterMail vM.6.01.05.02 201-2131-123-102-20050715) with ESMTP id
	<20060325015001.LZEU22606.imsm058dat.netvigator.com@n2.netvigator.com>;
	Sat, 25 Mar 2006 09:50:01 +0800
Received: from n2.netvigator.com ([127.0.0.1])
	by [127.0.0.1] with ESMTP (SpamPal v1.594)
	sender <kclau60@netvigator.com>; 25 Mar 2006 09:50:01 +????
Message-Id: <6.2.1.2.0.20060325094356.04de84d8@localhost>
X-Mailer: QUALCOMM Windows Eudora Version 6.2.1.2
Date: Sat, 25 Mar 2006 09:48:53 +0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: K C Lau <kclau60@netvigator.com>
Subject: Re: limitation using LIKE on ANY(array) 
Cc: pgsql-performance@postgresql.org
In-Reply-To: <5275.1143217555@sss.pgh.pa.us>
References: <200603170625.k2H6PJ926955@pillette.com>
	<1143112188.2810.27.camel@theo-laptop.int.coza.net.za>
	<20060323132448.GB4881@surnet.cl> <4423E90E.2090607@rentalia.com>
	<20060324125245.GB90527@pervasive.com>
	<4423EDA9.3090502@rentalia.com>
	<20060324130619.GF90527@pervasive.com>
	<20060324133252.GA22688@winnie.fuhr.org>
	<6.2.1.2.0.20060324225743.04d49ac0@localhost>
	<5275.1143217555@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.338 required=5 tests=[AWL=-0.378,
	DNS_FROM_RFC_POST=1.44, MAILTO_TO_SPAM_ADDR=0.276]
X-Spam-Score: 1.338
X-Spam-Level: *
X-Archive-Number: 200603/514
X-Sequence-Number: 17951

Thank you very much, Tom. We'll try it and report if there is any 
significant impact performance-wise.

Best regards,
KC.

At 00:25 06/03/25, Tom Lane wrote:
>K C Lau <kclau60@netvigator.com> writes:
> > Indeed, I get rejected even with:
> > .. WHERE ANY(array) = 'xx'
>
> > It would only work as documented in the manual (8.10.5):
> > SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
>
>That's not changing any time soon; the SQL spec defines only the second
>syntax for ANY, and I believe there would be syntactic ambiguity if we
>tried to allow the other.
>
> > With 8.1.3, I get an error when trying to do this on a Text[] column :
> > .. WHERE ANY(array) LIKE 'xx%'
>
>If you're really intent on doing that, make an operator for "reverse
>LIKE" and use it with the ANY on the right-hand side.
>
>regression=# create function rlike(text,text) returns bool as
>regression-# 'select $2 like $1' language sql strict immutable;
>CREATE FUNCTION
>regression=# create operator ~~~ (procedure = rlike, leftarg = text,
>regression(# rightarg = text, commutator = ~~);
>CREATE OPERATOR
>regression=# select 'xx%' ~~~ any(array['aaa','bbb']);
>  ?column?
>----------
>  f
>(1 row)
>
>regression=# select 'xx%' ~~~ any(array['aaa','xxb']);
>  ?column?
>----------
>  t
>(1 row)
>
>regression=#
>
>                         regards, tom lane