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.