1. table barang
ALTER TABLE barang
ALTER COLUMN kode_barang TYPE VARCHAR(11);
-- 2. Pastikan KODE_BARANG NOT NULL
ALTER TABLE barang
ALTER COLUMN kode_barang SET NOT NULL;
DROP PROCEDURE IF EXISTS generate_barang(INT);
CREATE OR REPLACE PROCEDURE generate_barang(IN jumlah INT)
LANGUAGE plpgsql
AS $$
DECLARE
mulai BIGINT;
BEGIN
SELECT COALESCE(
MAX(
NULLIF(
regexp_replace(kode_barang, '^ELK-', ''),
''
)::BIGINT
),
0
)
INTO mulai
FROM barang
WHERE kode_barang ~ '^ELK-[0-9]+$';
RAISE NOTICE 'Nomor ELK terakhir = %', mulai;
RAISE NOTICE 'Generate % data mulai dari ELK-%',
jumlah,
mulai + 1;
INSERT INTO barang
(
kode_barang,
nama_barang,
satuan_barang,
stok_barang
)
SELECT
'ELK-' || i::TEXT,
CASE ((i - 1) % 6)
WHEN 0 THEN 'RICE COOKER'
WHEN 1 THEN 'LEMARI ES'
WHEN 2 THEN 'TELEVISI'
WHEN 3 THEN 'RADIO/TAPE'
WHEN 4 THEN 'KOMPUTER'
WHEN 5 THEN 'KIPAS ANGIN'
END,
CASE ((i - 1) % 6)
WHEN 0 THEN 'BUAH'
WHEN 1 THEN 'UNIT'
WHEN 2 THEN 'UNIT'
WHEN 3 THEN 'BUAH'
WHEN 4 THEN 'UNIT'
WHEN 5 THEN 'BUAH'
END,
0
FROM generate_series(
mulai + 1,
mulai + jumlah
) AS g(i);
END;
$$;
CALL generate_barang(999994);
teguhth=# CALL generate_barang(999994);
NOTICE: Nomor ELK terakhir = 1000000
NOTICE: Generate 999994 data mulai dari ELK-1000001
CALL
teguhth=# CALL generate_barang(1000000);
NOTICE: Nomor ELK terakhir = 1999994
NOTICE: Generate 1000000 data mulai dari ELK-1999995
CALL
teguhth=# select count(*)from barang;
count
---------
2999994
(1 row)
teguhth=#
2. table suplier
ALTER TABLE suplier
ALTER COLUMN kode_suplier TYPE VARCHAR(10);
ALTER TABLE suplier
ALTER COLUMN kode_suplier SET NOT NULL;
DROP PROCEDURE IF EXISTS generate_suplier(INT);
CREATE OR REPLACE PROCEDURE generate_suplier(IN jumlah INT)
LANGUAGE plpgsql
AS $$
DECLARE
mulai BIGINT;
akhir BIGINT;
BEGIN
-- Cari nomor SUP terbesar yang sudah ada
SELECT COALESCE(
MAX(
CASE
WHEN kode_suplier ~ '^SUP-[0-9]+$'
THEN SUBSTRING(kode_suplier FROM 5)::BIGINT
ELSE 0
END
),
0
)
INTO mulai
FROM suplier;
akhir := mulai + jumlah;
RAISE NOTICE 'Nomor SUP terakhir = %', mulai;
RAISE NOTICE 'Generate % data mulai SUP-% sampai SUP-%',
jumlah,
mulai + 1,
akhir;
-- =====================================================
-- Generate data sekaligus menggunakan generate_series
-- =====================================================
INSERT INTO suplier
(
kode_suplier,
nama_suplier,
alamat_suplier,
kota_suplier,
telepon_suplier
)
SELECT
'SUP-' || LPAD(i::TEXT, 6, '0'),
'PT SUPLIER ' || i,
'JL INDUSTRI ' || (MOD(i, 100) + 1),
CASE MOD(i, 5)
WHEN 0 THEN 'JAKARTA'
WHEN 1 THEN 'BANDUNG'
WHEN 2 THEN 'BOGOR'
WHEN 3 THEN 'TANGERANG'
WHEN 4 THEN 'BEKASI'
END,
'(021) 8' || LPAD(
MOD(i, 10000000)::TEXT,
7,
'0'
)
FROM generate_series(
mulai + 1,
akhir
) AS g(i);
RAISE NOTICE 'Selesai generate % data.', jumlah;
END;
$$;
CALL generate_suplier(999994);
select count(*)from suplier;
3. table customer
ALTER TABLE customer
ALTER COLUMN kode_customer TYPE VARCHAR(10);
ALTER TABLE customer
ALTER COLUMN kode_customer TYPE VARCHAR(10),
ALTER COLUMN nama_customer TYPE VARCHAR(30),
ALTER COLUMN alamat_customer TYPE VARCHAR(20),
ALTER COLUMN kota_customer TYPE VARCHAR(20),
ALTER COLUMN telepon_customer TYPE VARCHAR(20);
DROP PROCEDURE IF EXISTS generate_customer(INT);
CREATE OR REPLACE PROCEDURE generate_customer(IN jumlah INT)
LANGUAGE plpgsql
AS $$
DECLARE
current_count BIGINT;
nomor_awal BIGINT;
jumlah_tambah BIGINT;
BEGIN
SELECT COUNT(*)
INTO current_count
FROM customer;
IF current_count >= jumlah THEN
RAISE NOTICE 'Data customer sudah % rows. Target = % rows.',
current_count, jumlah;
RETURN;
END IF;
-- Nomor berikutnya
nomor_awal := current_count + 1;
jumlah_tambah := jumlah - current_count;
RAISE NOTICE 'Data existing = %', current_count;
RAISE NOTICE 'Target = %', jumlah;
RAISE NOTICE 'Data yang ditambah = %', jumlah_tambah;
RAISE NOTICE 'Nomor customer mulai = %', nomor_awal;
INSERT INTO customer
(
kode_customer,
nama_customer,
alamat_customer,
kota_customer,
telepon_customer
)
SELECT
'C-' || LPAD(i::TEXT, 7, '0'),
'TOKO CUSTOMER ' || i,
'JL ABC ' || (MOD(i, 999) + 1),
CASE MOD(i, 5)
WHEN 0 THEN 'JAKARTA'
WHEN 1 THEN 'BANDUNG'
WHEN 2 THEN 'BOGOR'
WHEN 3 THEN 'TANGERANG'
WHEN 4 THEN 'BEKASI'
END,
'(021) 8' ||
LPAD(
MOD(i, 10000000)::TEXT,
7,
'0'
)
FROM generate_series(
nomor_awal,
nomor_awal + jumlah_tambah - 1
) AS g(i);
RAISE NOTICE 'Generate customer selesai.';
END;
$$;
CALL generate_customer(1000000);
4. table pasok
ALTER TABLE pasok
ALTER COLUMN kode_pasok TYPE VARCHAR(11);
-- =========================================================
-- GENERATE PASOK - POSTGRESQL
-- Target berdasarkan JUMLAH ROW yang diminta
-- =========================================================
DROP PROCEDURE IF EXISTS generate_pasok(INT);
CREATE OR REPLACE PROCEDURE generate_pasok(IN jumlah INT)
LANGUAGE plpgsql
AS $$
DECLARE
current_count BIGINT;
nomor BIGINT;
inserted_count BIGINT;
target_count BIGINT;
BEGIN
-- -----------------------------------------------------
-- Jumlah data yang sudah ada
-- -----------------------------------------------------
SELECT COUNT(*)
INTO current_count
FROM pasok;
target_count := current_count + jumlah;
RAISE NOTICE 'Data pasok existing = %', current_count;
RAISE NOTICE 'Data yang ditambah = %', jumlah;
RAISE NOTICE 'Target total = %', target_count;
-- -----------------------------------------------------
-- Cari nomor PAS terbesar.
--
-- TRIM() digunakan karena kalau kode_pasok CHAR(n),
-- PostgreSQL dapat menyimpan padding/spasi.
-- -----------------------------------------------------
SELECT COALESCE(
MAX(
CASE
WHEN TRIM(kode_pasok) ~ '^PAS-[0-9]+$'
THEN SUBSTRING(TRIM(kode_pasok) FROM 5)::BIGINT
ELSE NULL
END
),
0
)
INTO nomor
FROM pasok;
RAISE NOTICE 'Nomor PAS terakhir = %', nomor;
-- -----------------------------------------------------
-- Generate satu per satu hanya untuk kandidat kode.
--
-- ON CONFLICT DO NOTHING mencegah procedure berhenti
-- jika ada kode yang sudah ada.
-- -----------------------------------------------------
WHILE current_count < target_count LOOP
nomor := nomor + 1;
INSERT INTO pasok
(
kode_pasok,
kode_barang,
kode_suplier,
tanggal_pasok,
jumlah_pasok
)
VALUES
(
'PAS-' || LPAD(nomor::TEXT, 6, '0'),
'ELK-' ||
LPAD(
(MOD(nomor - 1, 6) + 1)::TEXT,
2,
'0'
),
CASE MOD(nomor - 1, 6)
WHEN 0 THEN 'EJ-01'
WHEN 1 THEN 'EJ-02'
WHEN 2 THEN 'EB-01'
WHEN 3 THEN 'EB-02'
WHEN 4 THEN 'EB-03'
WHEN 5 THEN 'EB-04'
END,
DATE '2002-01-01'
+ MOD(nomor - 1, 365)::INTEGER,
MOD(nomor - 1, 20) + 1
)
ON CONFLICT DO NOTHING;
-- -------------------------------------------------
-- Hitung apakah INSERT benar-benar berhasil
-- -------------------------------------------------
GET DIAGNOSTICS inserted_count = ROW_COUNT;
IF inserted_count > 0 THEN
current_count := current_count + inserted_count;
END IF;
-- -------------------------------------------------
-- Pengaman supaya tidak infinite loop
-- -------------------------------------------------
IF nomor > target_count + 10000000 THEN
RAISE EXCEPTION
'Generate dihentikan. Terlalu banyak kode yang conflict.';
END IF;
END LOOP;
RAISE NOTICE '==========================================';
RAISE NOTICE 'Generate PASOK selesai';
RAISE NOTICE 'Total data sekarang = %', current_count;
RAISE NOTICE '==========================================';
END;
$$;
CALL generate_pasok(1000000);
5. table pembelian
CREATE OR REPLACE PROCEDURE public.generate_pembelian(IN jumlah INTEGER)
LANGUAGE plpgsql
AS $procedure$
DECLARE
existing_count BIGINT;
nomor_awal BIGINT;
target BIGINT;
BEGIN
-- Jumlah data yang sudah ada
SELECT COUNT(*)
INTO existing_count
FROM pembelian;
-- Target total data
target := jumlah;
-- Kalau sudah mencapai target
IF existing_count >= target THEN
RAISE NOTICE
'Data pembelian sudah % baris. Target = %.',
existing_count,
target;
RETURN;
END IF;
-- Mulai dari nomor berikutnya
SELECT COALESCE(
MAX(
SUBSTRING(
TRIM(kode_pembelian)
FROM '^BEL-([0-9]+)$'
)::BIGINT
),
0
) + 1
INTO nomor_awal
FROM pembelian
WHERE TRIM(kode_pembelian) ~ '^BEL-[0-9]+$';
RAISE NOTICE
'Existing = %, Target = %, Nomor mulai = BEL-%',
existing_count,
target,
nomor_awal;
-- Generate data pembelian
INSERT INTO pembelian (
kode_pembelian,
kode_barang,
kode_customer,
tanggal_pembelian,
jumlah_pembelian
)
SELECT
'BEL-' || LPAD(i::TEXT, 6, '0'),
CASE MOD(i, 4)
WHEN 0 THEN 'ELK-01'
WHEN 1 THEN 'ELK-02'
WHEN 2 THEN 'ELK-03'
WHEN 3 THEN 'ELK-04'
END,
CASE MOD(i, 4)
WHEN 0 THEN 'J-0001'
WHEN 1 THEN 'J-0002'
WHEN 2 THEN 'B-0001'
WHEN 3 THEN 'B-0002'
END,
DATE '2002-01-01'
+ MOD(i, 365)::INTEGER,
MOD(i, 20) + 1
FROM generate_series(
nomor_awal,
nomor_awal + (target - existing_count) - 1
) AS g(i)
ON CONFLICT DO NOTHING;
RAISE NOTICE 'Selesai generate pembelian.';
END;
$procedure$;
CALL generate_pembelian(1000000);
No comments:
Post a Comment