regexp operator for array element compares needed

Started by Gauthier, Davealmost 14 years ago2 messagesgeneral
Jump to latest
#1Gauthier, Dave
dave.gauthier@intel.com

Hi:

v8.3.4 on linux

In plpgsql, I have something like this...

if ((string_to_array(check_values_text,',') <@ string_to_array((select permitted_values from bi_constraints where bicolumn = 'fivr'),',')) = 'f')

It's just testing all the values in the check_values_text csv are in permitted_values csv (through array operators as you can see)

I need to do the same thing only for regexp.
So, instead of...

if ((string_to_array('aa,cc,dx',',') <@ string_to_array((select permitted_values from 'aa,bb,cc,dd,ee' where bicolumn = 'fivr'),',')) = 'f')
(which would flag no compare because "dx" is not in 'aa,bb,cc,dd,ee')
I need to compare with regexp operator...

if ((string_to_array('aa,cc,dx',',') <@ string_to_array((select permitted_values from 'a.,b.,c*,d*,ee' where bicolumn = 'fivr'),',')) = 'f')
(which would compare OK because "dx" is is matched by "d*")

Don't even know if this sort of thing is possible. I didn't see any operator like this in the docs. May have to split out each and compare in nested loops sith atomic regexp compare "~".

Thanks for any ideas and/or help

#2Sergey Konoplev
gray.ru@gmail.com
In reply to: Gauthier, Dave (#1)
Re: regexp operator for array element compares needed

On Thu, Apr 12, 2012 at 10:50 PM, Gauthier, Dave
<dave.gauthier@intel.com> wrote:

... from 'a.,b.,c*,d*,ee'

You can not do like this.

Don't even know if this sort of thing is possible.  I didn't see any
operator like this in the docs.  May have to split out each and compare in
nested loops sith atomic regexp compare "~".

Probably you will find the ltree extension useful
http://www.postgresql.org/docs/9.1/interactive/ltree.html, though it
does not provide fully functional regexps.

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com
LinkedIn: http://ru.linkedin.com/in/grayhemp
JID/GTalk: gray.ru@gmail.com Skype: gray-hemp