Sa3PTnxR

1CREATE TABLE IF NOT EXISTS words (
2  word text
3);
4
5CREATE TABLE IF NOT EXISTS product (
6  product_id int not null, 
7  name text not null,
8  description text not null,
9  price decimal(12,2),
10  attributes jsonb,
11  primary key(product_id)  
12);
13
14CREATE TABLE IF NOT EXISTS offer (
15  product_id int not null, 
16  offer_id int not null,   
17  seller_id int not null,  
18  price decimal(12,2),
19  new bool,
20  primary key(product_id, offer_id)
21);
22
23CREATE OR REPLACE FUNCTION generate_products(num_products int)
24RETURNS SETOF product AS $function$
25DECLARE
26        all_words text[];
27BEGIN
28        SELECT array_agg(word) INTO all_words FROM words;
29
30        RETURN QUERY
31        SELECT series AS product_id,
32               generate_text(all_words,3) AS name,
33               generate_text(all_words,50) AS description,
34               (100*random())::numeric(12,2) AS price,
35               generate_attributes(all_words,20) AS attributes
36        FROM generate_series(1,num_products) series;
37END;
38$function$ LANGUAGE plpgsql;
39
40CREATE OR REPLACE FUNCTION generate_offers(num_offers int)
41RETURNS SETOF offer AS $function$
42
43        SELECT series AS offer_id,
44               (random()*10000000)::int AS product_id,
45               (random()*10000)::int AS seller_id,
46               100*random()::decimal(12,2) AS price,
47               random()::int::bool AS new
48        FROM generate_series(1,num_offers) series;
49
50$function$ LANGUAGE sql;
51
52CREATE OR REPLACE FUNCTION generate_attributes(words text[], num_attributes int)
53RETURNS jsonb AS $function$
54
55        SELECT ('{'||string_agg(format('"%s":"%s"',
56                                        words[ceil(array_length(words,1)*random())],
57                                        words[ceil(array_length(words,1)*random())]),',') ||'}')::jsonb
58        FROM generate_series(1,num_attributes);
59
60$function$ LANGUAGE sql;
61
62CREATE OR REPLACE FUNCTION generate_text(words text[], num_words int)
63RETURNS text AS $function$
64
65        SELECT string_agg(words[ceil(array_length(words,1)*random())],' ')
66        FROM generate_series(1,num_words);
67
68$function$ LANGUAGE sql;