-- Création de la table posts avec des données aléatoires

-- La table posts sera détruite et recréée !
\set ON_ERROR_STOP 1
\timing on

\set max_articles 5000
\set post_par_article 1000
-- Total 5 Mposts
\set max_len_post 200

SELECT setseed(0.42); -- reproductibilité

BEGIN ;

-- https://www.simononsoftware.com/random-string-in-postgresql/
CREATE OR REPLACE FUNCTION random_string_pavel(int)
RETURNS text
AS $$ 
  SELECT array_to_string(
    ARRAY (
      SELECT substring(
        '0123456789abcdefghijklmnopqrstuvwxyz' 
        FROM (random() *36)::int FOR 1)
      FROM generate_series(1, $1) ), '' ) 
$$ LANGUAGE sql;


CREATE TABLE posts
(   id_article int, -- future PK
    id_post int,
    ts timestamptz,
    message text
) ;

INSERT INTO posts
SELECT  id_article::int,
        (id_article * :post_par_article + id_post)::int,
        -- de 2000 à 2020 
        '2000-01-01 00:00:00'::timestamptz + (id_article * :post_par_article + id_post) * interval '2 minute' ts,
        random_string_pavel ((random()*:max_len_post)::int) AS message
FROM generate_series (1, :max_articles) AS id_article
CROSS JOIN generate_series (1, :post_par_article) AS id_post
-- pas de ORDER BY random car l'ordre est normalement à peu près temporel
;

\d+ posts

CREATE INDEX ON posts(ts) ;

\di+ posts*

COMMIT ;

