Re: regexp_replace to remove sql comments

Mike <mike(at)wolman(dot)co(dot)uk> writes:
> Thanks with a bit of moving stuff about I think thats sorted it - in
> case anyone every needs it:
> SELECT
> query,
> trim(regexp_replace(
> regexp_replace(
> regexp_replace(query,'\/\*.+\*\/','','g'),
> '--[^\r\n]*', ' ', 'g')
> , '\s+', ' ', 'g')) as q
> FROM public.pg_stat_statements
> WHERE dbid IN (SELECT oid FROM pg_database WHERE datname =
> current_database())

This doesn't look too reliable from here:

1. Doesn't handle multiline /* comments.

2. Does wrong thing if more than one /* comment appears on one line.
(You could improve that by using .*? instead of .+, but then it'd
do the wrong thing with nested /* comments.)

3. Breaks things if either -- or /* appear inside a string literal,
double-quoted identifier, or $$ literal.

I'm not at all sure that it's possible to handle this requirement 100%
correctly with regexes; they're unable to do context-sensitive processing.

But so far as pg_stat_statements is concerned, why would you need to
do this at all? The duplicate-query elimination it does should be
insensitive to comments already.

regards, tom lane