setup one CREATE OR REPLACE FUNCTION generate_test_data(num integer,table_name text) RETURNS void AS $BODY$ DECLARE query_sql text :=''; insert_sql text :=''; insert_sql_1 text :=''; insert_sql_2 text :=''; column_info record; type_name name :=''; column_name name :=''; BEGIN -- 根据表名查出各列的类型和列名 query_sql := 'select t.typname as type,a.attname as name from pg_class c,pg_attribute a,pg_type t where c.relname = '''|| table_name ||''' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid'; insert_sql_1 := 'insert into '||table_name||'('; insert_sql_2 := 'values('; for column_info in execute(query_sql) loop type_name := column_info.type; column_name := column_info.name; --根据类型写表达式拼insert语句 if substring(type_name from 1 for 3) = 'int' then insert_sql_1 := insert_sql_1 || column_name ||','; insert_sql_2 := insert_sql_2 || '(random()*(2*10^9))::integer' ||','; elsif type_name = 'text' then insert_sql_1 := insert_sql_1 || column_name ||','; insert_sql_2 := insert_sql_2 || 'substr(''abcdefghijklmnopqrstuvwxyz'',1,(random()*26)::integer)'||','; elsif type_name = 'char' then insert_sql_1 := insert_sql_1 || column_name ||',(random()*26)::integer)::char'||','; elsif substring(type_name from 1 for 5) = 'float' then insert_sql_1 := insert_sql_1 || column_name ||','; insert_sql_2 := insert_sql_2 || '(random()*(2*10^9))::float' ||','; elsif type_name = 'varchar' then insert_sql_1 := insert_sql_1 || column_name ||','; insert_sql_2 := insert_sql_2 || 'to_char(nextval(''seq_people_firstname''),''000000000000000000'')'||','; elsif type_name = 'date' then insert_sql_1 := insert_sql_1 || column_name ||','; insert_sql_2 := insert_sql_2 || 'date(generate_series(now(),now() + ''1 week'',''1 day''))' ||','; elsif type_name = 'time' then insert_sql_1 := insert_sql_1 || column_name ||','; insert_sql_2 := insert_sql_2 || 'generate_series(now(),''1 day'')::time' ||','; elsif type_name = 'timestamp' then insert_sql_1 := insert_sql_1 || column_name ||','; insert_sql_2 := insert_sql_2 || '(select generate_series(now()- interval ''730 days'',now(),''1 day'')::timestamp order by random() fetch first 1 rows only)' ||','; end if; end loop; --insert语句生成OK insert_sql_1 := substring(insert_sql_1 from 1 for char_length(insert_sql_1)-1)||')'; insert_sql_2 := substring(insert_sql_2 from 1 for char_length(insert_sql_2)-1)||')'; insert_sql := insert_sql_1 ||' '||insert_sql_2; raise notice '%',insert_sql; for i in 1..num loop --执行insert语句 --raise notice '%',i; execute(insert_sql); end loop; END;$BODY$ LANGUAGE plpgsql VOLATILE; setup two select generate_test_data(15000000,'people');