"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly. Specialization is for insects." (Robert A. Heinlein)

Wednesday, 5 November 2008

Generating random people with PostgreSQL

I just installed PostgreSQL on my PIII550 linux 'server' and I used the SQL example from my last post to do some test. Only the random select query and the procedure needed some little rework due to the different syntax:
Here is the random selection query
SELECT NAME, SEX,
                   CURRENT_DATE - CAST(RANDOM()*365.25*100 AS INTEGER) AS DOB,
                  (SELECT SURNAME FROM SURNAMES ORDER BY RANDOM() LIMIT 1) AS SURNAME
FROM NAMES ORDER BY RANDOM() LIMIT 1;
 Here is the procedure:
CREATE OR REPLACE FUNCTION FILL(IN NUM INT) RETURNS VARCHAR(1) AS $$
    DECLARE
          N INTEGER;
       BEGIN
              N:=NUM;
             WHILE N > 0 LOOP
                  INSERT INTO PEOPLE(NAME,SEX,DOB,SURNAME)
                  SELECT NAME, SEX,
                                     CURRENT_DATE - CAST(RANDOM()*365.25*100 AS INTEGER) AS DOB,
                                   (SELECT SURNAME FROM SURNAMES ORDER BY RANDOM() LIMIT 1) AS SURNAME
                 FROM NAMES ORDER BY RANDOM() LIMIT 1;
                N := N - 1;
       END LOOP;
       RETURN 'T';
END; $$
LANGUAGE PLPGSQL;
(Update)
Forgot to say it takes about seven seconds to insert ten thousands rows ... quite faster than MySQL.

test=# SELECT NOW();SELECT FILL(10000);SELECT NOW();
now
——————————-
2008-11-09 17:32:53.985751+01 (1 row)
fill
——
T
(1 row)
now
——————————-
2008-11-09 17:32:59.359207+01
(1 row)
test=#