PG的json字段是很好用的,但是,领导说是弄数据仓库还是什么的,非要把json字段拆分成15张表,脑残的需求昂,但是还是要做啊。贴存储过程的示例主要是展示存储过程的写法,逻辑性还是比较强的,存储函数,啊存储函数存储函数,学MySQL比较容易称呼成存储过程,PG里面是存储函数哈,存储函数不像java等开发语言那样有很多可以调用的函数,很多情况下要手动调整。大家借鉴啦。
原始表:org_raw_gs,org_raw_gz,org_raw_heb,org_raw_hen这样的表,org_raw_省份的后缀,表结构全部是一样的:
我们看一条记录:
主要是body字段,body是一个jsonb字段:
{“基本信息”: {“”: “”, “顺序”: [“企业名称”, “营业执照注册号”, “注册地址”, “工商登记机关”, “归类行业”, “注册资本(万元)”, “市场主体类型”, “经营范围”, “一般经营范围”, “成立日期”, “营业期限起始日期”, “营业期限到期日期”, “注册地址联系电话”, “法定代表人(负责人)姓名”, “企业类型”, “注册资金币种”, “核准日期”, “登记状态”, “经营状态”, “”], “企业名称”: “泰安路迎丰粮油店”, “企业类型”: “个体工商户”, “归类行业”: “”, “成立日期”: “2009-03-09 00:00:00”, “核准日期”: “2014-01-07 00:00:00”, “注册地址”: “金川区泰安路”, “登记状态”: “吊销”, “经营状态”: “”, “经营范围”: “米、面粉、植物油零售。”, “一般经营范围”: “”, “工商登记机关”: “金昌市工商行政管理局金川分局”, “市场主体类型”: “”, “注册资金币种”: “万人民币”, “营业执照注册号”: “620300600029787”, “注册地址联系电话”: “”, “注册资本(万元)”: “.5”, “营业期限到期日期”: “2013-03-08 00:00:00”, “营业期限起始日期”: “2009-03-09 00:00:00”, “法定代表人(负责人)姓名”: “王治秀”}}
把这个长字段贴到json.cn中可以看出他是一个两层的json结构,其中有的值是数组,现在我们就是做循环,把键值对拆成字段和值:
---------------------
--创建存储过程---------
---------------------
-- Function: public.split_json(character varying)
-- DROP FUNCTION public.split_json(character varying);
CREATE OR REPLACE FUNCTION split_json(tb_name varchar) RETURNS int AS
$$
DECLARE
xiangqing RECORD;
keyvalue_1 RECORD;
ifcolumn_exist int;
to_tb_name varchar(50);
text_var1 text;
text_var2 text;
text_var3 text;
BEGIN
to_tb_name:=concat('to_',tb_name);
EXECUTE 'drop table if exists '||to_tb_name;
EXECUTE 'create table '||to_tb_name||'(id varchar(100),org_id varchar(50),name varchar(80),time varchar(80),primary key(id))';
<<loop_to_table>>
FOR xiangqing IN EXECUTE 'SELECT id,org_id,name,body,time FROM '||tb_name::regclass||' where body is not null' LOOP
IF xiangqing.body ::varchar = '' THEN
RAISE NOTICE 'body is 空串';
ELSE
EXECUTE 'insert into '
||to_tb_name::regclass
||'(id,org_id,name,time) values ($1,$2,$3,$4)'
USING xiangqing.id,xiangqing.org_id,xiangqing.name,xiangqing.time;
<<loop_outer_key>>
FOR keyvalue_1 IN (select key,value::varchar from jsonb_each(xiangqing.body::jsonb)) LOOP
RAISE NOTICE '外层键--> %',keyvalue_1.key::varchar;
IF trim(keyvalue_1.key::varchar)<>'' THEN
--查看字段存在否,如果不存在,alter table添加字段
select count(*) INTO ifcolumn_exist from information_schema.columns where table_name = to_tb_name and column_name=keyvalue_1.key