Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Generate mock product data in PostgreSQL
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.