PostgreSQL9.4.26/Greenplum6.19.4下，写SQL语句，解析所有存储过程的文本，得到所涉及的表名、字段名

目录

01、需求：

02、SQL文本如下：

03、文中的正则表达式（PostgreSQL9.4.26）解释如下：

---

01、需求：

        现在有一个需求，需要在Greenplum6.19.4数据库中，解析其中所有存储过程（PostgreSQL中，存储过程就是函数，故本文沿用旧称）的文本，得到其中每一段的“insert into...(with)select...;”，然后进行解析，得到格式为“procedure,tar_name,col_name,src_name,param1...”这样的一张表。       

        我使用的手段，主要是字符串函数+正则表达式，以及最后取巧，使用字符串替换的办法，得到“select...”中包含的源表；步骤大致如下：

1. 去除两种注释，/*XXXXXX*/，--开头、\n换行结尾
2. 去除所有引号所含的内容，因为引号代表的字符串中，会包含英文分号，影响下一步截取
3. （提取存储过程中的所有参数，为额外需求，略）
4. 按英文分号;分割为多行
5. 通过截取、替换，获得“insert into...”分句中包含的目标表名、目标字段名
6. 通过正则表达式，获取from、join开头，含点号的特定字符串，即为源表名
7. 通过左连接取得的参数行，得到相关的参数列

02、SQL文本如下：

WITH temp1 AS (  --去除所有的/*XXXXXX*/类似的注释
SELECT row_number() OVER () AS id,
       p.proname,
       regexp_replace(p.prosrc, '/\*.*?\*/'::text, ''::text, 'g'::text) AS part,
       p.prosrc
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
-- WHERE n.nspname = ANY (ARRAY['prd_dwd'::name, 'prd_dws'::name, 'prd_ads'::name])
-- and p.proname ='f_c03_cux_rein_payin_out'
), 
temp2 AS (  --去除所有--开头、\n换行结尾的注释，匹配的正则表达式中？表示中间的字符去尽可能少
SELECT temp1.id,
temp1.proname,
regexp_replace(temp1.part, '--.*?\n'::text, ''::text, 'g'::text) AS part
FROM temp1
),
temp2_3 as (    --提取相关的参数行（declear~begin之间的部分参数行）

with temp2_1 as ( --先转文本为小写，再替换所有的换行与回车为空格，再截取declear~begin之间的部分，再按照分号“;”分割成多行
select id,proname,regexp_split_to_table(substring(regexp_replace(lower(part),E'[\\n\\r]+',' ','g') ,'declare(.*?)begin'),';') as part
from temp2
order by id,proname,part
),temp2_2 as (
select id,proname,row_number()over(partition by id order by part) as param,trim(regexp_replace(part,'\s+',' ','g')) as part
from temp2_1
)
select id,proname,    --因为我的需求中，参数的数量不多于5个，所以写死
max(case param when 1 then part else null end) param1,
max(case param when 2 then part else null end) param2,
max(case param when 3 then part else null end) param3,
max(case param when 4 then part else null end) param4,
max(case param when 5 then part else null end) param5
from temp2_2
group by id,proname

),
temp3 AS ( --去除所有单引号之间的内容，防止单引号之间字符串中出现分号;   影响下一步的截取，使得基础的一段insert into.... select .....的SQL文截取出错
SELECT temp2.id,
temp2.proname,
regexp_replace(temp2.part, '''[^''"]*'''::text, ''::text, 'g'::text) AS part   --''该注释无实际意义：使本行由于正则匹配中单引号的导致的错误显示效果，变得正常
FROM temp2   
), 
temp4 AS ( --按“;”去拆分，形成一行行的字符串
SELECT temp3.id,
temp3.proname,
regexp_split_to_table(temp3.part, ';'::text) AS part
FROM temp3
), 
temp5 AS (    --获取insert into 的文段
SELECT temp4.id,
temp4.proname,
row_number() OVER (PARTITION BY temp4.id) AS paragraph,
regexp_replace(upper(temp4.part), '[\n\r\s]+', ' ', 'g') AS part,
upper(temp4.part) AS origin_part
FROM temp4
WHERE temp4.part ~* 'INSERT[ ]+INTO'::text
), 
temp6 AS (    --获取目标表名(先去换行符、回车符，再截取INSERT与SELECT之间的字段；再替换INTO、空格、括号内的插入字段信息（XXX）为空)
              --获取目标表名之后，可能会出现的所有字段名，(a,b,c,d...)，没有则为“全部字段”
              --获取第一个出现的select后的部分(先替换换行符、回车符为空格，再截取SELECT后的部分，再在最后拼接一个空格（这是为了最终去与'表名+空格'去适配）
SELECT temp5.id,
temp5.paragraph,
temp5.proname,
regexp_replace(substring(temp5.part, 'INSERT(.*?)SELECT'::text), 'INTO| |\t|\([^()]*\)|WITH.*'::text, ''::text, 'g'::text) AS target_table,
regexp_split_to_table(coalesce(substring(replace(temp5.part, ' '::text, ''::text), 'INSERT[^\(\)]*\(([^\(\)]*)\)[^\(\)]*SELECT'::text),'全部字段'), ','::text)  AS tar_tab_col,
substring(temp5.part, 'SELECT(.*)'::text) || ' '::text AS part,
temp5.origin_part
FROM temp5
),     
temp7 AS (        --给所有的字段拍一个顺序
SELECT temp6.id,
temp6.proname,
temp6.paragraph,
temp6.target_table,
row_number() OVER (PARTITION BY temp6.id, temp6.paragraph) AS rn,
temp6.tar_tab_col,
temp6.part,
temp6.origin_part
FROM temp6
)
--利用正则表达式，匹配模式如下所示：
--截取FROM 开头，跟一个含点号的字符串，空格结尾中，那个含点号的字符串
--截取JOIN 开头，跟一个含点号的字符串，空格结尾中，那个含点号的字符串
--获取所有源表名，得到数组，再转为字符串

--最后加上所有参数名
SELECT temp7.id,
temp7.proname,
temp7.paragraph,
lower(temp7.target_table) AS target_table,
lower(temp7.tar_tab_col) AS tar_tab_col,
temp7.rn,
array_to_string(regexp_matches(lower(temp7.part), 'from ([^\. ]*?\.[^\. \)]*?)[ |\)]|join ([^\. ]*?\.[^\. \)]*?)[ |\)]'::text, 'gi'::text), ''::text, ''::text) AS source_table,
param1,param2,param3,param4,param5  
FROM temp7 
left join temp2_3 on temp7.proname=temp2_3.proname

03、文中的正则表达式（PostgreSQL9.4.26）解释如下（未更新）：

• regexp_replace(prosrc,'/\*.*?\*/','','g')

        '/\*.*?\*/' →→解析→ →'/—\*—.*?—\*—/'，其中：

        *号是特殊字符，故要加上反斜杠\，“\*”；

        “.*?”中，.：匹配任意单个字符（除了换行符），*：匹配前面的元素零次或多次，?：使前面的 * 变得非贪婪（lazy）模式，即尽可能少地匹配字符。

        含义：/*开头，*/结尾，中间的字符按最少量匹配

        作用：将procsrc列的字符串中，符合该正则表达式的子字符串，全局替换为空(“g”，不加，就只替换第一个)

• regexp_replace(part, '--.*?\n', E'\n', 'g')

        同理，正则表达式中，--开头，换行符\n结尾，中间的字符按最少量匹配，全局替换为换行符\n，此处出现的E可以不加，效果应该相同，只有使用\\n来表示\n时，才需要加。

• regexp_replace(lower(part), E'[\\n\\r]+', ' ', 'g') 

        方括号内框起来的多个字符，表示或者之意；

  +：匹配前面的元素一次或多次。

        E'\\n'中，这种的写法，表示将后方引号内的\\n转义为\n，以规避需要使用反斜杠\的情况下带来的歧义     

        故含义是，将连续的换行符\n、回车符\r，全局替换为一个空格；

• substring(XXXXXXX,'declare(.*?)begin')

      此处substring函数，可以直接使用正则匹配表达式来进行截取。

      此处使用正则表达式中的捕获组的概念，即将declare开头，begin结尾，中间的字符按最少量匹配后，得到的子字符串，只截取declare~begin之间的部分

• regexp_replace(part,'\s+' ,' ' ,'g')

       \s表示，匹配任何空白字符，包括空格' '、制表符\t、换行符\n、回车符\r等。

       含义是，将一个或多个空白，替换为一个空格

•  regexp_replace(part, E'\'[^\'"]*\'',  '','g') 

        E'\'[^\'"]*\''→→解析→ →E'\'—[^\'—"]*—\''

        此处使用的是单引号来写正则表达式，故内部的单引号'需要转义，即\'；[^\'"]表示，非单引号、非双引号的字符；*表示尽可能多的包含字符（此处似乎应该加?号，使用非贪婪匹配，尽可能少地匹配字符）

        E在此处，不知是否可不加。

        含义是，将“单引号'开头，单引号'结尾，中间的字符不含单双引号”，按最多量匹配，得到的子字符串，替换为空

• regexp_replace(XXXXXXX, 'INTO| |\([^()]*\)', '', 'g')

        'INTO| |\([^()]*\)'→→解析→ →'INTO| |\(—[^()]*—\)'

        竖杠|，表示或者之意，即将其中INTO字符串、空格、满足正则表达式'\([^()]*\)'的子字符串替换为空

        正则表达式'\([^()]*\)'，含义为，正括号(开头，反括号)结尾，中间内容不含正反括号()的字符串；

        最后将这三者替换为空。

• 其他涉及的正则表达式，均可从上文中，找出原型，故略。有些正则表达式，可能写的不那么简洁、精准了，读者可自行试验、优化。