regexp_replace to remove sql comments - Mailing list pgsql-general

Hi,

I am trying to clean up the query field returned by the
pg_stat_statements extension and remove all comments.

Some of the queries in the query field contain comments like '-- some
comment' and also '/* c style comments */'

I have managed to strip off the '--' comments and also white space but
after trying numerous regex for this via google but I am stuck.

WITH to_clean AS (
   SELECT
     regexp_replace(
        regexp_replace(trim(query), '--[^\r\n]*', '')  --clear up
comments like this one <-- this is ok
     , '\s+', ' ', 'g') as q    --clear up white space  <-- this is ok
   FROM public.pg_stat_statements
   WHERE dbid IN (SELECT oid FROM pg_database WHERE datname =
current_database())
)

SELECT  regexp_replace(q,'/\*.*\*/','') as q  /* strip off comments like
this */ <-- cannot get a regex to do this
FROM to_clean ORDER BY q


Im now thinking it may be better to do in a pgsql function as I think if
the comments are in queries then they need to be ignored.

Has anyone done anything like this?

Thanks,

Mike.