Array, ANY and Regular Expressions

"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