Generacion aleatoria de datos en postgres

random_data.sql

create or replace function random_number(length integer)
    returns text
as
$$
declare
    alphabet text[]  := '{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}';
    result   text    := '';
    i        integer := 0;
begin
    for i in 1..length
        loop
            result := result || alphabet[1 + random() * (array_length(alphabet, 1) - 1)];
        end loop;
    return result;
end ;
$$ language plpgsql;


create or replace function random_string(length integer)
    returns text
as
$$
declare
    alphabet text[]  := '{A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, Z, X, Y, Z,' ||
                        'a,b, c, d, e, f, g, h, i, j,k, l, m,n, o, p, q, r, s, t, u, v, z, x,y, w}';
    result   text    := '';
    i        integer := 0;
begin
    for i in 1..length
        loop
            result := result || alphabet[1 + random() * (array_length(alphabet, 1) - 1)];
        end loop;
    return result;
end ;
$$ language plpgsql;


create or replace function random_name()
    returns text
as
$$
declare
    names text[]  := '{Alan,	Jacinto,Alicia,Jesús,Mirta,Andrea,Josefina,Mónica,
        Andrés,Juan,Nicolás,Antonia,Juana,Noé,Antonio,Noelia,Azul,Julia,Paula,
        Bartolomé,Julián,Patricio,Belén,Juliana,Renzo,Celeste,Julio,Rodrigo
}';
begin
    return names[1 + random() * (array_length(names, 1) - 1)];
end ;
$$ language plpgsql;

create or replace function random_last_name()
    returns text
as
$$
declare
    names text[]  := '{Rodríguez,Vargas,Jiménez,Mora,Rojas,' ||
                     'González,Sánchez,Ramírez,Hernández,Castro}';
begin
    return names[1 + random() * (array_length(names, 1) - 1)];
end ;
$$ language plpgsql;

create or replace procedure random_people(q integer)
as
$$
declare
    i integer;
begin
    for i in 1..q loop
        insert into person(address, college_degree, email, id_number, last_name, name, phone, second_name, sex, workplace, college_graduate_id, country_id)
        values (random_string(100), random_string(30), random_string(20)||'@uclv.cu',random_number(11),
                random_last_name(), random_name(), random_number(6), random_last_name(), 'M', random_string(15), 3, 5);
    end loop;
end;
$$language plpgsql;

-- Ejemplos de uso

call random_people(1);

update person
set address = random_string(100);

update person
set name = random_name();

update person
set last_name   = random_last_name(),
    second_name = random_last_name();

update person
set phone = random_number(6);

update person
set email = random_string(10) || '@uclv.cu';

update person
set id_number = random_number(11);

update student
set work_phone = random_number(6);