Обсуждение: Array, ANY and Regular Expressions
I have a table with an array column (tags text[]) which I want to select from via ANY and RegExes. SELECT tags from zettelkasten where 'Sozialpsychologie' ~* any(tags) order by ident; delivers 7 rows SELECT tags from zettelkasten where 'SoziALPSychologie---FOOBARBAZ' ~* any(tags) order by ident; also delivers 7 rows, however: SELECT tags from zettelkasten where 'sozial' ~* any(tags) order by ident; delivers 0 rows. So how do I select all entries from "zettelkasten" tagged with e.g. '*psycholog*'? PS: Keep me CC:'ed, I am not subscribed to this list mit freundlichen Grüßen Stefan Schumacher -- https://deepsec.net/schedule/ Desing and Implementation of Security Awareness Campaigns at DeepSec Security Conference Vienna/Austria 2008-11-11
Вложения
"Stefan 'Kaishakunin' Schumacher" <stefan@net-tex.de> writes: > I have a table with an array column (tags text[]) which I want to > select from via ANY and RegExes. > SELECT tags from zettelkasten where 'Sozialpsychologie' ~* any(tags) order by > ident; Did you look at the matches? I suspect this isn't behaving even remotely the way you want, because ~* expects the pattern operand on the right. Since there's no SQL syntax with ANY() on the left of the comparison operator, what you'd need to do to make this work is invent a "reverse regex" operator that switches its arguments. That'd be pretty trivial to do with a one-line plpgsql function under it. (I'm not sure what performance would be like though; as the table grows you might find yourself needing to change the reversing function to C.) regards, tom lane
Also sprach Tom Lane (tgl@sss.pgh.pa.us) > "Stefan 'Kaishakunin' Schumacher" <stefan@net-tex.de> writes: > > I have a table with an array column (tags text[]) which I want to > > select from via ANY and RegExes. > > > SELECT tags from zettelkasten where 'Sozialpsychologie' ~* any(tags) order by > > ident; > > Did you look at the matches? I suspect this isn't behaving even > remotely the way you want, because ~* expects the pattern operand > on the right. Indeed, the results of ~* are not stable. > Since there's no SQL syntax with ANY() on the left of the comparison > operator, what you'd need to do to make this work is invent a "reverse > regex" operator that switches its arguments. That'd be pretty trivial > to do with a one-line plpgsql function under it. (I'm not sure what > performance would be like though; as the table grows you might find > yourself needing to change the reversing function to C.) So there is no builtin way to do a regex search in Arrays? mit freundlichen Grüßen Stefan Schumacher -- http://www.bildungswissenschaft.info http://www.open-source-tag.de -- Magdeburger Open-Source-Tag Entwicklung trifft Anwendung -- 11. Oktober 2008