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;

Hi marco(citus),

first, thanks for you fine sample. I've created the tables and the stored procedures in my (somewhat special i.e. mainframe) environment, and tried to generate some data into the tables with PostgreSQL code like this:

insert into product
(select generate_products(100)); <== PostgreSQL is somewhat specific; SELECT = EXEC(ute) stored procedure

I get following error message:

SQL Error [42804]: ERROR: column "product_id" is of type integer but expression is of type product
Hint: You will need to rewrite or cast the expression.
Position: 33

Do you have a hint please?

Regards
Gabor