一些自定义 PostgreSQL 随机数据生成器 —— Some self-defined PostgreSQL random data generators

相关文章推荐

暴走的小熊猫 · postgresql pivot - CSDN文库· 3 周前 ·

冷静的课本 · AnalyticDB ...· 1小时前 ·

耍酷的芹菜 · 运行bat时隐藏cmd窗口 - ...· 1 年前 ·

乐观的甜瓜 · iView-Modal - iT ...· 1 年前 ·

-- 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;
        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;
        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();