Regular expressions and arrays and ANY() question
I am trying to figure out how to use a regex and an ANY(), without any
luck, to determine if at least one element of an array (on the right)
matches the given constant pattern (on the left).
I think the problem is because the pattern expects to be on the right
side with the target on the left, but I want to do it reversed. (ie I
want 'Corvalli%' like 'Corvallis', but for postgres the only thing is
'Corvallis' like 'Corvalli%'). Has anybody worked around this before?
See below for code. TIA. Feel free to email me directly.
or_gis=# select * from quads_w_cities where 'Corvallis' = any
(cities); -- this works like I want
ohiocode | cities
----------+-------------------------------------
44123e2 | {Albany,Corvallis,Tangent,Estacada}
44123e3 | {Corvallis,Philomath}
(2 rows)
or_gis=# select * from quads_w_cities where 'corv.*' ~ any
(cities); -- I want this to give me something just like the above
ohiocode | cities
----------+--------
(0 rows)
or_gis=# select * from quads_w_cities where 'corv.*' ~~ any
(cities); -- etc...
ohiocode | cities
----------+--------
(0 rows)
or_gis=# select * from quads_w_cities where 'corv.*' ~* any
(cities);
ohiocode | cities
----------+--------
(0 rows)
or_gis=# select * from quads_w_cities where 'Corv.*' ~* any
(cities);
ohiocode | cities
----------+--------
(0 rows)
or_gis=# select * from quads_w_cities where '.*Corv.*' ~* any
(cities);
ohiocode | cities
----------+--------
(0 rows)
webb.sprague@gmail.com writes:
I am trying to figure out how to use a regex and an ANY(), without any
luck, to determine if at least one element of an array (on the right)
matches the given constant pattern (on the left).
I think the problem is because the pattern expects to be on the right
side with the target on the left, but I want to do it reversed.
Yeah, the ANY syntax only allows the array on the right. You'd have to
make a LIKE-ish operator that takes the pattern on the left ... it'd
take about two minutes to do this with a SQL or plpgsql function
underlying the operator, but such a function might not be fast enough
for you ...
regards, tom lane
On Tue, Jan 23, 2007 at 12:59:38AM -0500, Tom Lane wrote:
I think the problem is because the pattern expects to be on the right
side with the target on the left, but I want to do it reversed.Yeah, the ANY syntax only allows the array on the right. You'd have to
make a LIKE-ish operator that takes the pattern on the left ... it'd
take about two minutes to do this with a SQL or plpgsql function
underlying the operator, but such a function might not be fast enough
for you ...
If you created such a function, and made an operator with it that was a
communtator of LIKE (call it "is liked by"), would the planner be smart
enough to split the ANY and commutate it to the normal order?
i.e. convert:
foo "is_liked_by" ANY( 'bar', 'baz')
to
'bar' like foo OR 'baz' like foo.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout <kleptog@svana.org> writes:
If you created such a function, and made an operator with it that was a
communtator of LIKE (call it "is liked by"), would the planner be smart
enough to split the ANY and commutate it to the normal order?
No, at least not as of 8.2, because ANY translates as a ScalarArrayOpExpr
which only comes in the one flavor (array on the right).
regards, tom lane
On Tue, Jan 23, 2007 at 09:30:49AM -0500, Tom Lane wrote:
Martijn van Oosterhout <kleptog@svana.org> writes:
If you created such a function, and made an operator with it that
was a communtator of LIKE (call it "is liked by"), would the
planner be smart enough to split the ANY and commutate it to the
normal order?No, at least not as of 8.2, because ANY translates as a
ScalarArrayOpExpr which only comes in the one flavor (array on the
right).
How hard would it be to add the array-on-the-left flavor?
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!