postgreSQL为任何表生成随机数据

Question

如何在postgresql中为pgsql上的任何表生成随机数据CREATE OR REPLACE FUNCTION service.generate_arandom(sschema_etext，stable_name text，row_count int(

Answer 1

CREATE OR REPLACE FUNCTION service.generate_random(sschema_name text, stable_name text, row_count int)
RETURNS void
LANGUAGE plpgsql
VOLATILE
AS $$

declare
rec RECORD;
max_len int;
ssql text;
res_sql  text default '';
nmax_len int;
columns text [];
sels text [];
a int default 0;
begin
for rec in 
select column_name, data_type from information_schema.columns
where table_name = stable_name
and table_schema = sschema_name
and column_default is null
order by ordinal_position
loop
--raise notice 'colum = % data_type=%', rec.column_name, rec.data_type;
if rec.data_type= 'text' then
select floor(random() * (50-1+1) + 1)::int into nmax_len;
ssql = 'service.random_string('||nmax_len::text||') as '||rec.column_name;
elsif rec.data_type= 'uuid' then
select floor(random() * (50-1+1) + 1)::int into nmax_len;
ssql = 'md5(service.random_string('||nmax_len::text||'))::uuid as '||rec.column_name;
elsif rec.data_type= 'boolean' then
ssql = 'RANDOM()::INT::BOOLEAN as '||rec.column_name;       
elsif rec.data_type= 'date' then
ssql = '(date ''1956-01-01'' + random() * (now()-date ''1956-01-01 20:00:00''))::date as '||rec.column_name;
elsif rec.data_type= 'timestamp without time zone' then
ssql = 'timestamp ''epoch'' + (
extract(''epoch'' from timestamp ''1956-01-01 00:00:00'')
+ random() * (extract(''epoch'' from now())  - extract(''epoch'' from timestamp ''1956-01-01 00:00:00''))
) * interval ''1 second'' as '||rec.column_name;      
elsif rec.data_type in ('bigint','int8') then
select floor(random() * (19-1+1) + 1)::int into nmax_len;
ssql = 'floor(random() * ('||nmax_len||'-1+1) + 1)::'||rec.data_type||' as '||rec.column_name;
elsif rec.data_type in ('integer','int4','int') then
select floor(random() * (10-1+1) + 1)::int into nmax_len;
ssql = 'floor(random() * ('||nmax_len||'-1+1) + 1)::'||rec.data_type||' as '||rec.column_name;
end if;       
--raise notice 'colum = % ok', rec.column_name;
select array_append(sels, ssql) into sels;      
select array_append(columns, rec.column_name::text) into columns;

end loop;
res_sql:='insert into '||sschema_name||'.'||stable_name||'('||array_to_string(columns,',')||') select '||array_to_string(sels,',')||';';     
raise notice '%',res_sql;
for ii in 1..row_count loop
execute res_sql;
end loop;   

end;    
$$
EXECUTE ON ANY;