do $$
DECLARE
    _shop text;
    _period text := 'Month';
    _period_name text;
    _start_date date;
    _end_date date;
	_gender text;
	_country text;
	_category text;
	_brand text;
	_product_id text;
	_price double precision;
	_description text;
	_inserted_on timestamp;
	_rank bigint;
BEGIN
    FOR _period_name, _start_date, _end_date IN (
        with srcdata as ( select generate_series('2019-07-01'::date, '2019-10-01'::date, interval'1 month')::date as _date)
        select (trim(to_char(_date, 'Month'))||' '||to_char(_date, 'YYYY')) as _period_name, _date as _start_date, (_date+interval'1 month'-interval'1 day')::date as _end_date from srcdata
    ) LOOP
        FOR _shop IN (
            select unnest(string_to_array('shop1,shop11,shop21,shop31,shop41,shop51,shop61,shop71,shop81,shop91,shop101,shop111,shop121,shop131,shop141,shop151,shop161,shop171,shop181,shop191,shop201,shop211,shop221,shop231,shop241,shop251,shop261,shop271,shop281,shop291,shop301,shop321,shop331,shop341,shop351,shop631,shop641,shop651,shop661,shop671,shop701,shop801,shop911,shop1031,shop1121,shop1131,shop1141,shop1151,shop1161,shop1171,shop1181,shop1182,shop1183,shop1184,shop1185,shop1186,shop1187,shop1188,shop1189,shop1190,shop1191,shop1192,shop1193,shop1194,shop1195,shop1196,shop1197,shop1198,shop1199,shop1200,shop1201,shop1202,shop1203,shop1205,shop1206,shop1207,shop1208,shop1209,shop1210,shop1211,shop1212,shop1213,shop1214,shop1215,shop1216,shop1217,shop1219,shop1220,shop1221,shop1222,shop1223,shop1224,shop1225,shop1226,shop1227,shop1228,shop1229,shop1230,shop1231,shop1232,shop1233,shop1234,shop1235,shop1236,shop1237,shop1238,shop1239,shop1240,shop1241,shop1242,shop1243,shop1244,shop1245,shop1246,shop1247,shop1248,shop1249,shop1250,shop1251,shop1252,shop1253,shop1254,shop1255,shop1256,shop1257,shop1258,shop1259,shop1260,shop1261,shop1262,shop1263,shop1264,shop1265,shop1266,shop1267,shop1268,shop1269,shop1270,shop1271,shop1272,shop1274,shop1275,shop1276,shop1277,shop1278,shop1279,shop1280,shop1281,shop1282,shop1283,shop1284,shop1285,shop1286,shop1287,shop1288,shop1289,shop1290,shop1292,shop1293,shop1294,shop1295,shop1296,shop1297,shop1298,shop1299,shop1300,shop1423,shop1301,shop1302,shop1303,shop1304,shop1306,shop1307,shop1309,shop1310,shop1311,shop1312,shop1313,shop1314,shop1315,shop1316,shop1317,shop1318,shop1319,shop1320,shop1321,shop1322,shop1323,shop1324,shop1325,shop1326,shop1327,shop1328,shop1329,shop1332,shop1333,shop1335,shop1337,shop1338,shop1339,shop1340,shop1341,shop1342,shop1343,shop1344,shop1345,shop1346,shop1347,shop1348,shop1349,shop1350,shop1351,shop1352,shop1353,shop1354,shop1355,shop1356,shop1357,shop1358,shop1359,shop1360,shop1361,shop1362,shop1363,shop1364,shop1365,shop1366,shop1367,shop1368,shop1369,shop1371,shop1372,shop1373,shop1374,shop1375,shop1376,shop1377,shop1379,shop1380,shop1382,shop1383,shop1384,shop1385,shop1386,shop1387,shop1388,shop1389,shop1390,shop1391,shop1392,shop1393,shop1394,shop1396,shop1397,shop1398,shop1399,shop1400,shop1401,shop1402,shop1403,shop1404,shop1405,shop1407,shop1408,shop1409,shop1410,shop1411,shop1412,shop1414,shop1415,shop1416,shop1417,shop1418,shop1419,shop1420,shop1421,shop1422,shop1424,shop1425,shop1426,shop1427,shop1428,shop1429,shop1430,shop1431,shop1432,shop1433,shop1434,shop1435,shop1436,shop1437,shop1438,shop1439,shop1440,shop1441,shop1442,shop1443,shop1444,shop1445,shop1446,shop1447,shop1448,shop1449,shop1450,shop1451,shop1452,shop1453,shop1454,shop1455,shop1456,shop1457,shop1458,shop1459,shop1460,shop1461,shop1462,shop1463,shop1464,shop1465,shop1466,shop1467,shop1468,shop1469,shop1470,shop1471,shop1474,shop1475,shop1476,shop1477,shop1478,shop1479,shop1480,shop1481,shop1482,shop1483,shop1484,shop1485,shop1486,shop1487,shop1488,shop1489,shop1490,shop1491,shop1492,shop1493,shop1494,shop1495,shop1496,shop1497,shop1498,shop1499,shop1500,shop1501,shop1502,shop1503,shop1504,shop1505,shop1506,shop1507,shop1508,shop1509,shop1510,shop1511,shop1512,shop1513,shop1514,shop1515,shop1516,shop1517,shop1518,shop1519,shop1520,shop1521,shop1522,shop1523,shop1525,shop1526,shop1527,shop1528,shop1529,shop1530,shop1531,shop1532,shop1533,shop1534,shop1535,shop1536,shop1537,shop1538,shop1539,shop1540,shop1541,shop1542,shop1543,shop1544,shop1545,shop1546,shop1547,shop1548,shop1549',','))
        ) LOOP
                FOR _row_count IN 1..100 LOOP
                    _gender := substr(md5(random()::text),1,3);
                    _country := substr(md5(random()::text),1,6);
                    _category := substr(md5(random()::text),1,10);
                    _brand := md5(random()::text);
                    _product_id := md5(random()::text);
                    _price := random()*1000;
                    _description := md5(random()::text);
                    _inserted_on := clock_timestamp();
                    _rank := (random()*100)::int;

                    RAISE NOTICE '%: _shop: %, _period: %, _period_name: %, _start_date: %, _end_date: %, _gender: %, _country: %, _category:%, _brand: %, _product_id: %, _price: %, _description: %, _inserted_on: %, _rank: %',
                        clock_timestamp(), _shop, _period, _period_name, _start_date, _end_date, _gender, _country, _category, _brand, _product_id, _price, _description, _inserted_on, _rank;

                    INSERT INTO bi.test_multilevel
                    ( shop, period, period_name, start_date, end_date, gender, country, category, brand, product_id, price, description, inserted_on, rank)
                    VALUES ( _shop, _period, _period_name, _start_date, _end_date, _gender, _country, _category, _brand, _product_id, _price, _description, _inserted_on, _rank);
                END LOOP;
        END LOOP;
    END LOOP;
END;
$$

