Re: Array, ANY and Regular Expressions

"Stefan 'Kaishakunin' Schumacher" <stefan(at)net-tex(dot)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