Thread: 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