Skip to content

Instantly share code, notes, and snippets.

@koswarabilly

koswarabilly/duplicate schema postgresql

Last active August 4, 2020 01:36
Show Gist options
  • Save koswarabilly/86a7c2108d7272c5603bf05577bcd455 to your computer and use it in GitHub Desktop.
Save koswarabilly/86a7c2108d7272c5603bf05577bcd455 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
duplicate schema postgresql
CREATE OR REPLACE FUNCTION sch_p000.migrate_schema_table(p_to_schema character varying)
RETURNS boolean
LANGUAGE plpgsql
AS $function$DECLARE
v_query CHARACTER VARYING;
v_seq CHARACTER VARYING;
v_table CHARACTER VARYING;
v_col CHARACTER VARYING;
v_col_def CHARACTER VARYING;
v_routine CHARACTER VARYING;
v_routine_param CHARACTER VARYING;
v_routine_def CHARACTER VARYING;
v_routine_type CHARACTER VARYING;
v_trig_name CHARACTER VARYING;
v_trig_agg CHARACTER VARYING;
v_trig_obj_table CHARACTER VARYING;
v_trig_obj_action CHARACTER VARYING;
v_trig_orientation CHARACTER VARYING;
v_trig_timing CHARACTER VARYING;
v_ret bool;
BEGIN
v_ret := FALSE;
FOR v_seq IN
SELECT a.sequence_name
FROM information_schema."sequences" a
WHERE a.sequence_schema NOT IN ('information_schema', 'pg_catalog') AND a.sequence_schema = 'sch_p000'
LOOP
v_query := 'CREATE SEQUENCE ' || p_to_schema || '.' || v_seq || ' NO MINVALUE NO MAXVALUE';
EXECUTE v_query;
END LOOP;
FOR v_table IN
SELECT a.table_name, a.table_type
FROM information_schema."tables" a
WHERE a.table_schema NOT IN ('information_schema', 'pg_catalog') AND a.table_schema = 'sch_p000' AND a.table_type = 'BASE TABLE'
LOOP
v_query := 'CREATE TABLE ' || p_to_schema || '.' || v_table || ' AS SELECT * FROM sch_p000.' || v_table || ' WHERE FALSE';
EXECUTE v_query;
FOR v_col_def, v_col IN
SELECT
pg_get_expr(d.adbin, d.adrelid) AS default_value,
a.attname
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
WHERE NOT a.attisdropped AND a.attnum > 0 AND a.attrelid = ('sch_p000' || '.' || v_table)::regclass AND pg_get_expr(d.adbin, d.adrelid) IS NOT NULL
LOOP
v_query := 'ALTER TABLE ' || p_to_schema || '.' || v_table || ' ALTER COLUMN ' || v_col || ' SET DEFAULT ' || REPLACE(v_col_def, 'sch_p000', p_to_schema);
EXECUTE v_query;
END LOOP;
END LOOP;
FOR v_routine, v_routine_def, v_routine_type IN
SELECT a.routine_name, REPLACE(a.routine_definition, 'sch_p000', p_to_schema), a.data_type
FROM information_schema."routines" a
WHERE a.routine_schema NOT IN ('information_schema', 'pg_catalog') AND a.routine_schema = 'sch_p000' AND (
a.routine_name <> 'migrate_schema_table' AND
a.routine_name <> 'migrate_schema_constraint' AND
a.routine_name <> 'migrate_schema_view' AND
a.routine_name <> 'migrate_schema_foreign_key'
)
LOOP
BEGIN
SELECT pg_catalog.pg_get_function_identity_arguments(p.oid)
INTO v_routine_param
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = v_routine AND n.nspname = 'sch_p000';
END;
v_query := format(
$string$
CREATE OR REPLACE FUNCTION
$string$ || p_to_schema || '.' || v_routine || '(' || v_routine_param || ') ' ||
$string$
RETURNS
$string$ ||
v_routine_type ||
$string$
language plpgsql as $f$
$string$ ||
v_routine_def ||
$string$
$f$
$string$
);
EXECUTE v_query;
END LOOP;
FOR v_trig_name, v_trig_agg, v_trig_obj_table, v_trig_obj_action, v_trig_orientation, v_trig_timing IN
SELECT
tr.trigger_name,
string_agg(tr.event_manipulation, ' OR '),
tr.event_object_table,
REPLACE(tr.action_statement, 'sch_p000', p_to_schema),
tr.action_orientation,
tr.action_timing
FROM information_schema.triggers tr
WHERE tr.event_object_schema = 'sch_p000'
GROUP BY tr.trigger_name, tr.event_object_table, tr.action_statement, tr.action_orientation, tr.action_timing
LOOP
v_query := 'CREATE TRIGGER ' || v_trig_name || ' ' || v_trig_timing ||
' ' || v_trig_agg || ' ON ' || p_to_schema || '.' || v_trig_obj_table ||
' FOR EACH ' || v_trig_orientation || ' ' || v_trig_obj_action;
EXECUTE v_query;
END LOOP;
v_ret := TRUE;
RETURN v_ret;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END;$function$
;
CREATE OR REPLACE FUNCTION sch_p000.migrate_schema_constraint(p_to_schema character varying)
RETURNS boolean
LANGUAGE plpgsql
AS $function$DECLARE
v_query CHARACTER VARYING;
v_constraint_name CHARACTER VARYING;
v_constraint_table CHARACTER VARYING;
v_constraint_type CHARACTER VARYING;
v_constraint_column CHARACTER VARYING;
v_constraint_r_table CHARACTER VARYING;
v_constraint_r_column CHARACTER VARYING;
v_ret bool;
BEGIN
v_ret := FALSE;
FOR v_constraint_table, v_constraint_column IN
SELECT
tc.table_name,
REPLACE(cc.check_clause, 'IS NOT NULL', '')
FROM information_schema.check_constraints cc
JOIN information_schema.table_constraints tc ON tc.constraint_name = cc.constraint_name
WHERE cc.constraint_schema NOT IN ('pg_catalog', 'information_schema') AND cc.constraint_schema = 'sch_p000'
LOOP
v_query := 'ALTER TABLE ' || p_to_schema || '.' || v_constraint_table || ' ALTER COLUMN ' || v_constraint_column || ' SET NOT NULL';
EXECUTE v_query;
END LOOP;
FOR v_constraint_table, v_constraint_name, v_constraint_type, v_constraint_column IN
SELECT
tc.table_name,
tc.constraint_name,
tc.constraint_type,
string_agg(kcu.column_name, ', ')
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON kcu.constraint_name = tc.constraint_name AND kcu.table_schema = tc.table_schema
WHERE tc.constraint_schema = 'sch_p000' AND tc.constraint_type <> 'FOREIGN KEY'
GROUP BY tc.table_name, tc.constraint_name, tc.constraint_type
ORDER BY tc.constraint_type desc
LOOP
v_query := 'ALTER TABLE ' || p_to_schema || '.' || v_constraint_table || ' ADD CONSTRAINT ' || v_constraint_name || ' ' ||
v_constraint_type || ' (' || v_constraint_column || ')' ;
EXECUTE v_query;
END LOOP;
v_ret := TRUE;
RETURN v_ret;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END;$function$
;
CREATE OR REPLACE FUNCTION sch_p000.migrate_schema_foreign_key(p_to_schema character varying)
RETURNS boolean
LANGUAGE plpgsql
AS $function$DECLARE
v_query CHARACTER VARYING;
v_constraint_name CHARACTER VARYING;
v_constraint_table CHARACTER VARYING;
v_constraint_type CHARACTER VARYING;
v_constraint_column CHARACTER VARYING;
v_constraint_r_table CHARACTER VARYING;
v_constraint_r_column CHARACTER VARYING;
v_ret bool;
BEGIN
v_ret := FALSE;
FOR v_constraint_table, v_constraint_name, v_constraint_r_table, v_constraint_r_column IN
SELECT
tc.table_name,
tc.constraint_name,
ccu.table_name AS foreign_table_name,
string_agg(DISTINCT ccu.column_name, ', ') AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'sch_p000'
GROUP BY tc.table_name, tc.constraint_name, ccu.table_name
LOOP
v_query := 'ALTER TABLE ' || p_to_schema || '.' || v_constraint_table || ' ADD CONSTRAINT ' || v_constraint_name || ' FOREIGN KEY' ||
' (' || v_constraint_r_column || ') REFERENCES ' || p_to_schema || '.' || v_constraint_r_table ||
'(' || v_constraint_r_column || ') ON UPDATE CASCADE ON DELETE CASCADE';
EXECUTE v_query;
END LOOP;
v_ret := TRUE;
RETURN v_ret;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END;$function$
;
CREATE OR REPLACE FUNCTION sch_p000.migrate_schema_view(p_to_schema character varying)
RETURNS boolean
LANGUAGE plpgsql
AS $function$DECLARE
v_query CHARACTER VARYING;
v_view CHARACTER VARYING;
v_view_def CHARACTER VARYING;
v_ret bool;
BEGIN
v_ret := FALSE;
FOR v_view, v_view_def IN
SELECT
v.table_name,
v.view_definition
FROM information_schema."views" v
WHERE v.table_schema NOT IN ('information_schema', 'pg_catalog') AND v.table_schema = 'sch_p000'
ORDER BY v.table_name DESC
LOOP
v_query := format(
$string$
CREATE OR REPLACE VIEW
$string$ ||
p_to_schema || '.' || v_view ||
$string$
AS
$string$ ||
v_view_def
);
EXECUTE v_query;
END LOOP;
FOR v_view, v_view_def IN
SELECT
v.table_name,
REPLACE(v.view_definition, 'sch_p000', p_to_schema)
FROM information_schema."views" v
WHERE v.table_schema NOT IN ('information_schema', 'pg_catalog') AND v.table_schema = 'sch_p000'
ORDER BY v.table_name DESC
LOOP
v_query := format(
$string$
CREATE OR REPLACE VIEW
$string$ ||
p_to_schema || '.' || v_view ||
$string$
AS
$string$ ||
v_view_def
);
EXECUTE v_query;
END LOOP;
v_ret := TRUE;
RETURN v_ret;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END;$function$
;
CREATE OR REPLACE FUNCTION sch_p001.extract_transaksi(p_tgl_awal CHARACTER VARYING, p_tgl_akhir CHARACTER VARYING, p_serial CHARACTER VARYING, p_tipe_transaksi int2, p_jenis_transaksi CHARACTER VARYING)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
DECLARE
v_id_jenis_transaksi CHARACTER VARYING;
v_id_tran CHARACTER VARYING;
v_id_periode CHARACTER VARYING;
v_tgl_periode date;
v_nilai_saldo numeric(16, 5);
v_kode_cabang CHARACTER VARYING;
v_no_jurnal CHARACTER VARYING;
v_rekening CHARACTER VARYING;
v_ret bool;
BEGIN
v_ret := FALSE;
-- a9bb47cc649b85e895d2234e789e992b
IF (p_serial = 'IMBT') THEN
FOR v_id_periode, v_tgl_periode, v_nilai_saldo, v_kode_cabang, v_no_jurnal, v_rekening IN
WITH q_jurnal AS (
SELECT
a.id_tran,
sum(a.nilai_debit) AS nilai_saldo
FROM sch_p001.trd_jurnal a
GROUP BY a.id_tran
)
SELECT
b.id_periode,
b.tgl_periode,
c.nilai_saldo,
a.kode_cabang,
a.no_tran,
g.no_rekening
FROM sch_p001.trh_jurnal a
LEFT JOIN sch_p001.mhs_periode_kasir b ON a.tgl_tran = b.tgl_periode AND b.kode_cabang = a.kode_cabang
LEFT JOIN q_jurnal c ON a.id_tran = c.id_tran
LEFT JOIN sch_p001.trd_jurnal d ON a.id_tran = d.id_tran AND d.nilai_debit > 0
LEFT JOIN sch_p001.mhs_akun e ON d.id_akun = e.id_akun
LEFT JOIN sch_p001.mhs_bank f ON replace(replace(e.nama_akun, 'BANK ', ''), ' INDONESIA', '') = f.kode_bank
LEFT JOIN sch_p001.mhs_rekening_perusahaan g ON f.kode_bank = g.kode_bank
WHERE a.tgl_tran > CAST(p_tgl_awal AS date) AND a.tgl_tran < CAST(p_tgl_akhir AS date) AND a.keterangan LIKE (p_serial || '%')
LOOP
SELECT md5(random()::text || now()::text) INTO v_id_tran;
INSERT INTO sch_p001.trh_transaksi (id_tran, tgl_tran, kode_cabang, id_periode, id_kasir, id_jenis_transaksi, nilai_jenis_transaksi, tipe_transaksi, no_jurnal, saldo_transaksi, input_by, input_dt)
VALUES (v_id_tran, v_tgl_periode, v_kode_cabang, v_id_periode, 'a9bb47cc649b85e895d2234e789e992b', p_jenis_transaksi, v_rekening, p_tipe_transaksi, v_no_jurnal, v_nilai_saldo, 'ADMIN', now());
END LOOP;
ELSIF (p_serial = 'IMBK') THEN
FOR v_id_periode, v_tgl_periode, v_nilai_saldo, v_kode_cabang, v_no_jurnal, v_rekening IN
WITH q_jurnal AS (
SELECT
a.id_tran,
sum(a.nilai_kredit) AS nilai_saldo
FROM sch_p001.trd_jurnal a
GROUP BY a.id_tran
)
SELECT
b.id_periode,
b.tgl_periode,
c.nilai_saldo,
a.kode_cabang,
a.no_tran,
g.no_rekening
FROM sch_p001.trh_jurnal a
LEFT JOIN sch_p001.mhs_periode_kasir b ON a.tgl_tran = b.tgl_periode AND b.kode_cabang = a.kode_cabang
LEFT JOIN q_jurnal c ON a.id_tran = c.id_tran
LEFT JOIN sch_p001.trd_jurnal d ON a.id_tran = d.id_tran AND d.nilai_kredit > 0
LEFT JOIN sch_p001.mhs_akun e ON d.id_akun = e.id_akun
LEFT JOIN sch_p001.mhs_bank f ON replace(replace(e.nama_akun, 'BANK ', ''), ' INDONESIA', '') = f.kode_bank
LEFT JOIN sch_p001.mhs_rekening_perusahaan g ON f.kode_bank = g.kode_bank
WHERE a.tgl_tran > CAST(p_tgl_awal AS date) AND a.tgl_tran < CAST(p_tgl_akhir AS date) AND a.keterangan LIKE (p_serial || '%')
LOOP
SELECT md5(random()::text || now()::text) INTO v_id_tran;
INSERT INTO sch_p001.trh_transaksi (id_tran, tgl_tran, kode_cabang, id_periode, id_kasir, id_jenis_transaksi, nilai_jenis_transaksi, tipe_transaksi, no_jurnal, saldo_transaksi, input_by, input_dt)
VALUES (v_id_tran, v_tgl_periode, v_kode_cabang, v_id_periode, 'a9bb47cc649b85e895d2234e789e992b', p_jenis_transaksi, v_rekening, p_tipe_transaksi, v_no_jurnal, v_nilai_saldo, 'ADMIN', now());
END LOOP;
ELSIF (p_serial = 'IMJT') THEN
FOR v_id_periode, v_tgl_periode, v_nilai_saldo, v_kode_cabang, v_no_jurnal IN
WITH q_jurnal AS (
SELECT
a.id_tran,
sum(a.nilai_debit) AS nilai_saldo
FROM sch_p001.trd_jurnal a
GROUP BY a.id_tran
)
SELECT
b.id_periode,
b.tgl_periode,
c.nilai_saldo,
a.kode_cabang,
a.no_tran
FROM sch_p001.trh_jurnal a
LEFT JOIN sch_p001.mhs_periode_kasir b ON a.tgl_tran = b.tgl_periode AND b.kode_cabang = a.kode_cabang
LEFT JOIN q_jurnal c ON a.id_tran = c.id_tran
WHERE a.tgl_tran > CAST(p_tgl_awal AS date) AND a.tgl_tran < CAST(p_tgl_akhir AS date) AND a.keterangan LIKE (p_serial || '%')
LOOP
SELECT md5(random()::text || now()::text) INTO v_id_tran;
INSERT INTO sch_p001.trh_transaksi (id_tran, tgl_tran, kode_cabang, id_periode, id_kasir, id_jenis_transaksi, tipe_transaksi, no_jurnal, saldo_transaksi, input_by, input_dt, lbr_giro)
VALUES (v_id_tran, v_tgl_periode, v_kode_cabang, v_id_periode, 'a9bb47cc649b85e895d2234e789e992b', p_jenis_transaksi, p_tipe_transaksi, v_no_jurnal, v_nilai_saldo, 'ADMIN', now(), 1);
END LOOP;
ELSIF (p_serial = 'IMJK') THEN
FOR v_id_periode, v_tgl_periode, v_nilai_saldo, v_kode_cabang, v_no_jurnal IN
WITH q_jurnal AS (
SELECT
a.id_tran,
sum(a.nilai_debit) AS nilai_saldo
FROM sch_p001.trd_jurnal a
GROUP BY a.id_tran
)
SELECT
b.id_periode,
b.tgl_periode,
c.nilai_saldo,
a.kode_cabang,
a.no_tran
FROM sch_p001.trh_jurnal a
LEFT JOIN sch_p001.mhs_periode_kasir b ON a.tgl_tran = b.tgl_periode AND b.kode_cabang = a.kode_cabang
LEFT JOIN q_jurnal c ON a.id_tran = c.id_tran
WHERE a.tgl_tran > CAST(p_tgl_awal AS date) AND a.tgl_tran < CAST(p_tgl_akhir AS date) AND a.keterangan LIKE (p_serial || '%')
LOOP
SELECT md5(random()::text || now()::text) INTO v_id_tran;
INSERT INTO sch_p001.trh_transaksi (id_tran, tgl_tran, kode_cabang, id_periode, id_kasir, id_jenis_transaksi, tipe_transaksi, no_jurnal, saldo_transaksi, input_by, input_dt, lbr_giro)
VALUES (v_id_tran, v_tgl_periode, v_kode_cabang, v_id_periode, 'a9bb47cc649b85e895d2234e789e992b', p_jenis_transaksi, p_tipe_transaksi, v_no_jurnal, v_nilai_saldo, 'ADMIN', now(), 1);
END LOOP;
ELSE
FOR v_id_periode, v_tgl_periode, v_nilai_saldo, v_kode_cabang, v_no_jurnal IN
WITH q_jurnal AS (
SELECT
a.id_tran,
sum(a.nilai_debit) AS nilai_saldo
FROM sch_p001.trd_jurnal a
GROUP BY a.id_tran
)
SELECT
b.id_periode,
b.tgl_periode,
c.nilai_saldo,
a.kode_cabang,
a.no_tran
FROM sch_p001.trh_jurnal a
LEFT JOIN sch_p001.mhs_periode_kasir b ON a.tgl_tran = b.tgl_periode AND b.kode_cabang = a.kode_cabang
LEFT JOIN q_jurnal c ON a.id_tran = c.id_tran
WHERE a.tgl_tran > CAST(p_tgl_awal AS date) AND a.tgl_tran < CAST(p_tgl_akhir AS date) AND a.keterangan LIKE (p_serial || '%')
LOOP
SELECT md5(random()::text || now()::text) INTO v_id_tran;
INSERT INTO sch_p001.trh_transaksi (id_tran, tgl_tran, kode_cabang, id_periode, id_kasir, id_jenis_transaksi, tipe_transaksi, no_jurnal, saldo_transaksi, input_by, input_dt)
VALUES (v_id_tran, v_tgl_periode, v_kode_cabang, v_id_periode, 'a9bb47cc649b85e895d2234e789e992b', p_jenis_transaksi, p_tipe_transaksi, v_no_jurnal, v_nilai_saldo, 'ADMIN', now());
END LOOP;
END IF;
v_ret := TRUE;
RETURN v_ret;
EXCEPTION
WHEN OTHERS
THEN
raise notice '% %', SQLERRM, SQLSTATE;
RETURN false;
END;$function$;
SELECT sch_p001.extract_transaksi('2019-12-31', '2020-01-06', 'IMJT', CAST(0 AS int2), '35612e6aa810ed732f776eb3d7e79247');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment