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

On Tue, Jan 25, 2005 at 04:28:06PM +0100, Vincenzo Ciancia wrote:

> 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.

It sounds like you're looking for the equivalent of Perl's quotemeta:

% perl -le 'print quotemeta "abc.*"'
abc\.\*

I'm not aware of any such function in PostgreSQL, but you could use
a PL/Perl function that simply calls quotemeta:

CREATE FUNCTION quotemeta(text) RETURNS text AS '
return quotemeta $_[0];
' LANGUAGE plperl IMMUTABLE STRICT;

SELECT quotemeta('abc.*');
quotemeta
-----------
abc\.\*
(1 row)

There might be differences between PostgreSQL's and Perl's regular
expression engines, but perhaps not enough to matter in this case.

I expect it would be easy to add such a function to PostgreSQL, so
consider suggesting it to the developers or even writing it yourself
and submitting a patch.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/