一些自定义 PostgreSQL 随机数据生成器 —— Some self-defined PostgreSQL random data generators
1. 生成随机整数 —— Generate a random integer
-- Function: -- Generate a random integer -- Parameters: -- min_value: Minimum value -- max_value: Maximum value create or replace function gen_random_int(min_value int default 1, max_value int default 1000) returns int as $$ begin return min_value + round((max_value - min_value) * random()); end; $$ language plpgsql;
select gen_random_int();
select gen_random_int(1,10);
2. 生成随机字母字符串 —— Generate a random alphabetical string
-- Function: -- Generate a random alphabetical string -- Parameters: -- str_length: Length of the string -- letter_case: Case of letters. Values for option: lower, upper and mixed create or replace function gen_random_alphabetical_string(str_length int default 10, letter_case text default 'lower') returns text as $body$ begin if letter_case in ('lower', 'upper', 'mixed') then return case letter_case when 'lower' then array_to_string(array(select substr('abcdefghijklmnopqrstuvwxyz',(ceil(random()*26))::int, 1) FROM generate_series(1, str_length)), '') when 'upper' then array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',(ceil(random()*26))::int, 1) FROM generate_series(1, str_length)), '') when 'mixed' then array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*52))::int, 1) FROM generate_series(1, str_length)), '') else array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*52))::int, 1) FROM generate_series(1, str_length)), '') end; else RAISE EXCEPTION 'value % for parameter % is not recognized', letter_case, 'letter_case' Using Hint = 'Use "lower", "upper" or "mixed". The default value is "lower"', ERRCODE ='22023'; end if; end; $body$ language plpgsql volatile;
select gen_random_alphabetical_string(10);
select gen_random_alphabetical_string('lower');
3. 生成随机字符串 —— Generate a random alphanumeric string
-- Function: -- Generate a random alphanumeric string -- Parameters: -- str_length: Length of the string create or replace function gen_random_string(str_length int default 10) returns text as $body$ select array_to_string(array(select substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*62))::int, 1) FROM generate_series(1, $1)), ''); $body$ language sql volatile;
select gen_random_string(10)
4. 生成随机时间戳 —— Generate a random timestamp
-- Function: -- Generate a random timestamp
-- Parameters: -- start_time: Lower bound of the time -- end_time: Upper bound of the time create or replace function gen_random_timestamp(start_time timestamp default date_trunc('year', now()), end_time timestamp default now()) returns timestamp as $$ begin return start_time + round((extract(epoch from end_time)- extract(epoch from start_time))* random()) * interval '1 second'; end; $$ language plpgsql;
select gen_random_timestamp();
select gen_random_timestamp('2017-10-22 10:05:33','2017-10-22 10:05:35');
5. 生成随机整型数组 —— Generate a random integer array
-- Function: -- Generate a random integer array -- Parameters: -- max_value: Maximum value of the elements -- max_length: Maximum length of the array -- fixed_length: Whether the length of array is fixed. If it is true, the length of array will match max_length. create or replace function gen_random_int_array(max_value int default 1000, max_length int default 10, fixed_length bool default true ) returns int[] as $$ begin return case when not fixed_length then array(select ceil(random()*max_value)::int from generate_series(1,ceil(random()*max_length)::int)) else array(select ceil(random()*max_value)::int from generate_series(1,max_length)) end ; end; $$ LANGUAGE plpgsql
6. 生成随机字符串数组 —— Generate a random string array
-- Function: -- Generate a random string array -- Parameters: -- str_length: Length of string -- max_length: Maximum length of the array -- fixed_length: Whether the length of array is fixed. If it is true, the length of array will match max_length. create or replace function gen_random_string_array(str_length int default 10, max_length int default 10, fixed_length bool default TRUE ) returns text[] as $$ declare v_array text[]; declare v_i int; begin v_array := array[]::text[]; if fixed_length then for v_i in select generate_series(1, max_length) loop v_array := array_append(v_array,array_to_string(array(select substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*62))::int, 1) FROM generate_series(1, str_length)), '')); end loop; else for v_i in select generate_series(1,ceil(random()* max_length)::int) loop v_array := array_append(v_array,array_to_string(array(select substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',(ceil(random()*62))::int, 1) FROM generate_series(1, str_length)), '')); end loop; end if; return v_array; end; $$ language plpgsql
select gen_random_string_array()
select gen_random_string_array(10,5,true);
7. 从整数数组中随机选择一个元素 —— Randomly select one element from an integer array
-- Function: -- Randomly select one element from an integer array create or replace function select_random_one(list int[]) returns int as $$ declare v_length int := array_length(list, 1); begin return list[1+round((v_length-1)*random())]; end; $$ language plpgsql;
select select_random_one(array[1,2,3,4]);
8. 从字符串数组中随机选择一个元素 —— Randomly select one element from an string-array
-- Function: -- Randomly select one element from an string-array -- str_length: Length of string create or replace function select_random_one(list text[]) returns text as $$ declare v_length int := array_length(list, 1); begin return list[1+round((v_length-1)*random())]; end; $$ language plpgsql;
select select_random_one(array['abc','def','ghi']);
9. 随机生成汉字字符串 —— Generate a random Chinese string
-- Generate a random Chinese string create or replace function gen_ramdom_chinese_string(str_length int) returns text as $$ declare my_char char; char_string varchar := ''; i int := 0; begin while (i < str_length) loop -- chinese 19968..40869 my_char = chr(19968 + round(20901 * random())::int); char_string := char_string || my_char; i = i + 1; end loop; return char_string; end; $$ language plpgsql;
10. 随机手机号码生成器，11位手机号 —— Generate a random mobile number
-- Generate a random mobile number create or replace function gen_random_mobile_number() returns text as $body$ select 1 || string_agg(col,'') from (select substr('0123456789',(ceil(random()*10))::int, 1) as col FROM generate_series(1, 10)) result; $body$ language sql volatile;
select gen_random_mobile_number();
