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.