pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlimitation using LIKE on ANY(array)
3+ messages / 2 participants
[nested] [flat]
* limitation using LIKE on ANY(array)
@ 2006-03-24 15:25 K C Lau <kclau60@netvigator.com>
2006-03-24 16:25 ` Re: limitation using LIKE on ANY(array) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: K C Lau @ 2006-03-24 15:25 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: limitation using LIKE on ANY(array)
2006-03-24 15:25 limitation using LIKE on ANY(array) K C Lau <kclau60@netvigator.com>
@ 2006-03-24 16:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-25 01:48 ` Re: limitation using LIKE on ANY(array) K C Lau <kclau60@netvigator.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2006-03-24 16:25 UTC (permalink / raw)
To: K C Lau <kclau60@netvigator.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: limitation using LIKE on ANY(array)
2006-03-24 15:25 limitation using LIKE on ANY(array) K C Lau <kclau60@netvigator.com>
2006-03-24 16:25 ` Re: limitation using LIKE on ANY(array) Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-25 01:48 ` K C Lau <kclau60@netvigator.com>
0 siblings, 0 replies; 3+ messages in thread
From: K C Lau @ 2006-03-25 01:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-03-25 01:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-24 15:25 limitation using LIKE on ANY(array) K C Lau <kclau60@netvigator.com>
2006-03-24 16:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-25 01:48 ` K C Lau <kclau60@netvigator.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox