Skip to content

Instantly share code, notes, and snippets.

@toshsan

toshsan/generate-products.sql

Forked from marcocitus/generate-products.sql
Created November 23, 2020 13:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save toshsan/995bccba026e1a8d6035f1b17b0bc357 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save toshsan/995bccba026e1a8d6035f1b17b0bc357 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @marcocitus marcocitus revised this gist Apr 22, 2016. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions generate-products.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -29,10 +29,10 @@ BEGIN

    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
    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;
    @@ -41,10 +41,10 @@ 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
    (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;
  2. @marcocitus marcocitus revised this gist Apr 22, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion generate-products.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -29,7 +29,7 @@ BEGIN

    RETURN QUERY
    SELECT series AS product_id,
    generate_text(all_words,2) AS name,
    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
  3. @marcocitus marcocitus revised this gist Apr 21, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion generate-products.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -32,7 +32,7 @@ BEGIN
    generate_text(all_words,2) AS name,
    generate_text(all_words,50) AS description,
    (100*random())::numeric(12,2) AS price,
    generate_attributes(words,20) AS attributes
    generate_attributes(all_words,20) AS attributes
    FROM generate_series(1,num_products) series;
    END;
    $function$ LANGUAGE plpgsql;
  4. @marcocitus marcocitus revised this gist Apr 21, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion generate-products.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -32,7 +32,7 @@ BEGIN
    generate_text(all_words,2) AS name,
    generate_text(all_words,50) AS description,
    (100*random())::numeric(12,2) AS price,
    generate_attributes() AS attributes
    generate_attributes(words,20) AS attributes
    FROM generate_series(1,num_products) series;
    END;
    $function$ LANGUAGE plpgsql;
  5. @marcocitus marcocitus revised this gist Apr 13, 2016. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions generate-products.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,7 @@
    CREATE TABLE IF NOT EXISTS words (
    word text
    );

    CREATE TABLE IF NOT EXISTS product (
    product_id int not null,
    name text not null,
  6. @marcocitus marcocitus created this gist Apr 13, 2016.
    64 changes: 64 additions & 0 deletions generate-products.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,64 @@
    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,2) AS name,
    generate_text(all_words,50) AS description,
    (100*random())::numeric(12,2) AS price,
    generate_attributes() 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;