Обсуждение: Quering by Regular Expressions stored in table

Hi! I have a table with a character column (called match_regex)
containing a POSIX regular expression, hoping to have Postgres use it
while evaluating the "where" predicate. The following error occurs:

SELECT *
from patterns
where 'blah blah blah' ~ match_regex;

ERROR:  invalid regular expression: quantifier operand invalid

We are using Postgres 7.4.8. I just tried the above on Postgres 8.0.4
and it seems to work! Is there a way to do this on my older version? If
not, maybe I can leverage this to get Postgres upgraded ;-)

Is seems that I need a regular expression type on the right of the
operator. Perhaps I can cast this 'character varying(100) column' to a
regular expression type if there is such a thing... though I have not
found one in the docs?
   where 'blah blah blah' ~ match_regex::regularexpression;

Thanks,
Allen

Allen Fair <dba@girders.org> writes:
> Hi! I have a table with a character column (called match_regex)
> containing a POSIX regular expression, hoping to have Postgres use it
> while evaluating the "where" predicate. The following error occurs:

> SELECT *
> from patterns
> where 'blah blah blah' ~ match_regex;

> ERROR:  invalid regular expression: quantifier operand invalid

This means there's at least one value in the column that's not a legal
regex (* at the front is one possible cause, IIRC).

            regards, tom lane

Oops, I apoligize for the noise, this does seem to be working. I had an
invalid regular expression in the column.

Allen

Allen Fair wrote:
> Hi! I have a table with a character column (called match_regex)
> containing a POSIX regular expression, hoping to have Postgres use it
> while evaluating the "where" predicate. The following error occurs:
>
> SELECT *
> from patterns
> where 'blah blah blah' ~ match_regex;
>
> ERROR:  invalid regular expression: quantifier operand invalid
>
> We are using Postgres 7.4.8. I just tried the above on Postgres 8.0.4
> and it seems to work! Is there a way to do this on my older version? If
> not, maybe I can leverage this to get Postgres upgraded ;-)
>
> Is seems that I need a regular expression type on the right of the
> operator. Perhaps I can cast this 'character varying(100) column' to a
> regular expression type if there is such a thing... though I have not
> found one in the docs?
>   where 'blah blah blah' ~ match_regex::regularexpression;
>
> Thanks,
> Allen
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>