Thread: 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/
> 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!