Re: Array, ANY and Regular Expressions

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