Исключение группы в регулярных выражениях (non-capturing group)

(?:from|join)[ \r\n\t]{0,}([a-z._]{0,})
?: begin non-capturing group
Выделенная группа не будет учитываться ...

пример получения всех таблиц из накопленной статистики запросов

       
with t1 as (

select queryid, calls, lower(query) as full_query, replace((regexp_matches(lower(query), '(?: from | join | lateral )[ \r\n\t]{0,}(["a-z0-9._]{0,})','gm'))[1],'"','') as foundedfulltable

from pg_stat_statements

where lower(query) not like '%function%'

)

, t2 as (

select queryid, calls, regexp_replace(full_query, E'[\\n\\r\\t\\u2028]+', ' ', 'gm' ) as full_query, foundedfulltable

,case when strpos(foundedfulltable,'.')>0 then (regexp_match(foundedfulltable, '([a-z_]{1,})\.{1}(?:[a-z0-9_]{1,})'))[1] else null end as foundedschema

,case when strpos(foundedfulltable,'.')>0 then (regexp_match(foundedfulltable, '(?:[a-z_]{1,})\.{1}([a-z0-9_]{1,})'))[1] else foundedfulltable end as foundedtable

from t1 where coalesce(foundedfulltable,'') != ''

)

-- select *, pg_typeof(foundedfulltable) from t2

, t3 as (

select pn.nspname as schemaname, pc.relname as objectname

from pg_catalog.pg_class pc

inner join pg_catalog.pg_namespace pn on pn.oid = pc.relnamespace

where relkind in ('r', 'v')

)



select distinct t2.calls, t3.*

from t2

inner join t3 on t3.schemaname = coalesce(t2.foundedschema,'public') and t3.objectname = t2.foundedtable

where not starts_with(t2.foundedtable, 'pg_')

order by 1 desc