Re: Validating user-input to be inserted in regular expressions

Sean Davis wrote:

> Yes, this is a fine place to ask your question.  Sometimes it does take
> a day or two to get an answer.  Will quote_literal do what you want?
>
> http://www.postgresql.org/docs/8.0/interactive/functions-string.html
>
> Search on that page for quote_literal.
>

Thank you for your answer. Unfortunately quote_literal is not what I am
looking for, in fact it quotes special characters in the sense of strings,
not in the sense of regular expressions. Here's some example to explain my
problem a little better:

I would like to select strings that begin with 'a.', so I do NOT want the
following (suppose 'a.' is generic user input)

relfs=# select true where 'aa' ~ ('a.'||'.*');
bool
------
t

I could as well use 'a\\.', but what characters should I escape? Surely
$^+.*[] and possibly others. The function quote_literal does:

relfs=# select true where 'aa' ~ (quote_literal('a.')||'.*');
bool
------
(0 righe)

but it's mere illusion :) In fact we have:

relfs=# select true where '\'aa\'' ~ (quote_literal('a.')||'.*');
bool
------
t

Thanks for any suggestions

Vincenzo

--
Please note that I do not read the e-mail address used in the from field but
I read vincenzo_ml at yahoo dot it
Attenzione: non leggo l'indirizzo di posta usato nel campo from, ma leggo
vincenzo_ml at yahoo dot it