Thread: regexp searching in arrays not working?
Hi all,
have a column which is of type text[]. the following works:
select * from table where 'Transformer' = ANY (thiscol)
this also works
select * from table where 'Transformer' ~ ANY (thiscol)
however if i have a partial string like so
select * from table where 'Trans' ~ ANY (thiscol)
it returns nothing.
Is regex searching not functioning (as i expect it to?) i searched the docs and didnt see anything about it.
Regards
Rhys
have a column which is of type text[]. the following works:
select * from table where 'Transformer' = ANY (thiscol)
this also works
select * from table where 'Transformer' ~ ANY (thiscol)
however if i have a partial string like so
select * from table where 'Trans' ~ ANY (thiscol)
it returns nothing.
Is regex searching not functioning (as i expect it to?) i searched the docs and didnt see anything about it.
Regards
Rhys
"Rhys Stewart" <rhys.stewart@gmail.com> writes: > Is regex searching not functioning (as i expect it to?) ~ expects the pattern on the right, not the left. So it's taking your array entries as patterns, which don't match the data 'Trans'. Since there's no "(array) ANY op scalar" syntax, the only way to get this to work is to make a reverse-pattern-match operator that takes the pattern on the left. You can glue one together from spare parts easily enough: regression=# create function reverse_regex(text,text) returns bool as regression-# 'select $2 ~ $1' language sql immutable strict; CREATE FUNCTION regression=# create operator ~~~ (procedure = reverse_regex, regression(# leftarg = text, rightarg = text); CREATE OPERATOR but I'm not sure what the performance will be like with a SQL function in there... regards, tom lane
thats cool,
thanks.
thanks.
2007/6/19, Tom Lane <tgl@sss.pgh.pa.us>:
"Rhys Stewart" <rhys.stewart@gmail.com> writes:
> Is regex searching not functioning (as i expect it to?)
~ expects the pattern on the right, not the left. So it's taking your
array entries as patterns, which don't match the data 'Trans'.
Since there's no "(array) ANY op scalar" syntax, the only way to get
this to work is to make a reverse-pattern-match operator that takes
the pattern on the left. You can glue one together from spare parts
easily enough:
regression=# create function reverse_regex(text,text) returns bool as
regression-# 'select $2 ~ $1' language sql immutable strict;
CREATE FUNCTION
regression=# create operator ~~~ (procedure = reverse_regex,
regression(# leftarg = text, rightarg = text);
CREATE OPERATOR
but I'm not sure what the performance will be like with a SQL function
in there...
regards, tom lane
On Tue, Jun 19, 2007 at 07:36:02PM -0400, Tom Lane wrote: > "Rhys Stewart" <rhys.stewart@gmail.com> writes: > > Is regex searching not functioning (as i expect it to?) > > ~ expects the pattern on the right, not the left. So it's taking your > array entries as patterns, which don't match the data 'Trans'. > > Since there's no "(array) ANY op scalar" syntax, Why isn't there? Is it forbidden by the SQL standard? Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes: > On Tue, Jun 19, 2007 at 07:36:02PM -0400, Tom Lane wrote: >> Since there's no "(array) ANY op scalar" syntax, > Why isn't there? Is it forbidden by the SQL standard? Well, the spec has <quantified comparison predicate> ::= <row value expression> <comp op> <quantifier> <table subquery> <quantifier> ::= <all> | <some> <all> ::= ALL <some> ::= SOME | ANY and not the reverse. The array syntax is an abuse of this notation and doesn't have the reverse either. I'm not really sure how much new code would be needed to support the other case, or whether it would be worth the trouble. regards, tom lane