Generate mock product data in PostgreSQL

generate-products.sql

CREATE TABLE IF NOT EXISTS words (
  word text
);

CREATE TABLE IF NOT EXISTS product (
  product_id int not null, 
  name text not null,
  description text not null,
  price decimal(12,2),
  attributes jsonb,
  primary key(product_id)  
);

CREATE TABLE IF NOT EXISTS offer (
  product_id int not null, 
  offer_id int not null,   
  seller_id int not null,  
  price decimal(12,2),
  new bool,
  primary key(product_id, offer_id)
);

CREATE OR REPLACE FUNCTION generate_products(num_products int)
RETURNS SETOF product AS $function$
DECLARE
        all_words text[];
BEGIN
        SELECT array_agg(word) INTO all_words FROM words;

        RETURN QUERY
        SELECT series AS product_id,
               generate_text(all_words,3) AS name,
               generate_text(all_words,50) AS description,
               (100*random())::numeric(12,2) AS price,
               generate_attributes(all_words,20) AS attributes
        FROM generate_series(1,num_products) series;
END;
$function$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION generate_offers(num_offers int)
RETURNS SETOF offer AS $function$

        SELECT series AS offer_id,
               (random()*10000000)::int AS product_id,
               (random()*10000)::int AS seller_id,
               100*random()::decimal(12,2) AS price,
               random()::int::bool AS new
        FROM generate_series(1,num_offers) series;

$function$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION generate_attributes(words text[], num_attributes int)
RETURNS jsonb AS $function$

        SELECT ('{'||string_agg(format('"%s":"%s"',
                                        words[ceil(array_length(words,1)*random())],
                                        words[ceil(array_length(words,1)*random())]),',') ||'}')::jsonb
        FROM generate_series(1,num_attributes);

$function$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION generate_text(words text[], num_words int)
RETURNS text AS $function$

        SELECT string_agg(words[ceil(array_length(words,1)*random())],' ')
        FROM generate_series(1,num_words);

$function$ LANGUAGE sql;