Re: Regex query not using index

Tino,

My users are developers and the goal was to accept a simple
comma-delimited list of string values as a function's input parameter.
The function would then parse this input param into a valid regex
expression.

I was trying to write a function that lets me avoid using Execute
<string> and instead write in-line SQL with all the benefits of
pre-compilation and optimization.

Regex offers such a technique- IF it could understand regex that
represented a set of logical ORs and do an index scan (my rule is to
avoid seq-scans)

An example of regex that allows you to use in-line SQL with a
condition equivalent to many OR conditions when using basic comparison
operators:

select * from table1
where name ~ '.*' '^Smith$' |^Jones$':

And this works very well- except for the seq scan instead of an index scan

On Feb 20, 2008 2:31 AM, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> Postgres User wrote:
> > im trying to allow the client to pass a varchar param into my
> > function, and want to avoid any parsing of the parameter inside the
> > function, or code to build a sql string.
> >
> > if the function can use this code, it will be compiled and optimized
> > (unlike a dynamic sql stirng)
> >
> > select * from mytable where fielda ~ p_param
>
> No, you should never let users specify raw regex. at best they can
> hog down your server. Regex is a state engine and you can create
> endless loops.
>
> Maybe we can see the overall picture of your query?
>
> Regards
> Tino
>