function_parse.sql

/*
  create a JavaScript UDF to find regexp matches and throw them into an array
*/
create or replace function regexp_matches(TEXT string, PATTERN string)
  returns variant
  language javascript
    as '
      var re = new RegExp(PATTERN, "g");
      res = [];
      while (m = re.exec(TEXT)) {
          res.push(m[1]);
      }
      return res
     ';

/*
  create temporary table, parsing raw SQL text, finding function matches, and then exploding array elements into rows
*/
create table function_calls as
  select dpo:"JobDPO:primary":"uuid"::string as uuid
    , dpo:"JobDPO:description":"accountId"::int as account_id
    , dpo:"JobDPO:description":"description"::string as sql_text
    , value::string as function
  from job_raw as job,
  lateral flatten(regexp_matches(dpo:"JobDPO:description":"description"::string, '\\b((?![as]+)[\\w]+)(\\s)?\\('))
  where dpo:"JobDPO:description":"accountId"::int in (176, 228, 413, 429, 497, 637, 753, 1001, 1121, 2021);

/*
  get list of top functions
*/
select lower(function) as function
  , count(*)
from function_calls
where lower(function) not in ('and', 'in', 'as', 'from', 'on', 'or')
group by 1
order by 2 desc
limit 500;