表字段脱敏导出（随机字符串、随机数）

## 26个大小写字母，随机生成字符串
create or replace function random_string(integer)

returns text as

$body$

         select array_to_string(array(select substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' FROM (ceil(random()*62))::int FOR 1) FROM generate_series(1, $1)), '');

$body$

language sql volatile; 

测试：
select random_string(10);

##生成9位随机数字
select  (random()*(10^9))::integer

##生成84 +  9位随机数
select  '84' || (random()*(10^9))::integer

#转换脱敏字段，并根据原有表字段类型转换字段类型

create table tableName_temp as (

select id,
(case when coalesce(name_full,'') = '' then null else random_string(10)::varchar end) as name_full,
date_birth,
(case when coalesce(ident_card,'') = '' then null else (random()*(10^9))::varchar end) as ident_card,
date_applied,
(case when coalesce(mobile_phone,'') = '' then null else ('84' || (random()*(10^9))::integer)::varchar end) as mobile_phone,
count_paid,cnt_days_past_due,
(case when coalesce(home_phone,'') = '' then null else ('84' || (random()*(10^9))::integer)::varchar end) as home_phone,
(case when coalesce(work_phone,'') = '' then null else ('84' || (random()*(10^9))::integer)::varchar end) as work_phone,
(case when coalesce(phone1,'') = '' then null else ('84' || (random()*(10^9))::integer)::varchar end) as phone1,
(case when coalesce(phone2,'') = '' then null else ('84' || (random()*(10^9))::integer)::varchar end) as phone2,
(case when coalesce(phone3,'') = '' then null else ('84' || (random()*(10^9))::integer)::varchar end) as phone3,
status
 from tableName );

#将public.black_data导入public.tableName_temp
insert into public.black_data_temp
(id,name_full,date_birth,ident_card,date_applied,mobile_phone,count_paid,cnt_days_past_due,home_phone,
work_phone,phone1,phone2,phone3,status) 
select id,name_full,date_birth,ident_card::integer,date_applied,mobile_phone,count_paid,cnt_days_past_due,home_phone,
work_phone,phone1,phone2,phone3,status from public.tableName;