PostgreSQL Query Helper

query.txt

drop user darkogl_developer;

create role group_darkogl_support;

CREATE USER darkogl_support
with 
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
PASSWORD 'glsupport';

alter group group_darkogl_support add user darkogl_support;

grant all on sch_dc_system.view_user_akses to group_darkocenter_developer;

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'dba_dev_gl' -- ← change this to your DB
  AND pid <> pg_backend_pid();

CREATE SEQUENCE sch_p000.seq_trh_posting
NO MINVALUE
NO MAXVALUE;

ALTER SEQUENCE sch_p000.seq_trh_posting RESTART;

ALTER TABLE sch_p000.view_neraca_saldo OWNER TO darkogl_developer;

drop table sch_p000.mhs_riwayat_saldo_akun_periode;
drop view sch_p000.view_neraca_saldo;
drop sequence sch_p000.seq_mhs_kategori_akun;
drop trigger trig_trd_jurnal_after on sch_p000.trd_jurnal;
drop function sch_p000.set_posting_jurnal;

select sch_p000.get_serial_transaksi('TR-POSTING', 'ADMIN');
select sch_p000.set_tutup_periode('3df5f0685ebf5c967d49d053e58cff6d');
select sch_p000.set_posting_jurnal('ba3e6621afd2d92c4d21be795c2d57b4');
select sch_p000.set_batal_posting_jurnal('1ee98a2807bb40ec743114466717ac94', 'ADMIN');
select sch_p000.set_saldo_awal('1.1', 1000.2, 0, 'ba3e6621afd2d92c4d21be795c2d57b4', 'ADMIN') as saldo_awal;

SELECT md5(random()::text || now()::text) AS id;
select lpad(cast(1 as character varying), 2, '0');

grant all on sch_p000.view_buku_besar to group_darkogl_developer;

grant all privileges on all TABLES in schema sch_p000 to group_darkogl_developer;

alter table sch_dc_system.mhd_user_perusahaan drop constraint mhd_user_perusahaan_mhm_user_fk;

select
column_name::json->'key'
from schema_name.table_name;

nextval('sch_erp_inventory.seq_trh_penjualan'::regclass)

drop user darkogl_developer;

create role group_darkogl_support;

CREATE USER darkogl_support
with 
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
PASSWORD 'glsupport';

alter group group_darkogl_support add user darkogl_support;

grant all on sch_dc_system.view_user_akses to group_darkocenter_developer;

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'dba_dev_gl' -- ← change this to your DB
  AND pid <> pg_backend_pid();

CREATE SEQUENCE sch_p000.seq_trh_posting
NO MINVALUE
NO MAXVALUE;

ALTER SEQUENCE sch_p000.seq_trh_posting RESTART;

ALTER TABLE sch_p000.view_neraca_saldo OWNER TO darkogl_developer;

drop table sch_p000.mhs_riwayat_saldo_akun_periode;
drop view sch_p000.view_neraca_saldo;
drop sequence sch_p000.seq_mhs_kategori_akun;
drop trigger trig_trd_jurnal_after on sch_p000.trd_jurnal;
drop function sch_p000.set_posting_jurnal;

select sch_p000.get_serial_transaksi('TR-POSTING', 'ADMIN');
select sch_p000.set_tutup_periode('3df5f0685ebf5c967d49d053e58cff6d');
select sch_p000.set_posting_jurnal('ba3e6621afd2d92c4d21be795c2d57b4');
select sch_p000.set_batal_posting_jurnal('1ee98a2807bb40ec743114466717ac94', 'ADMIN');
select sch_p000.set_saldo_awal('1.1', 1000.2, 0, 'ba3e6621afd2d92c4d21be795c2d57b4', 'ADMIN') as saldo_awal;

SELECT md5(random()::text || now()::text) AS id;
select lpad(cast(1 as character varying), 2, '0');

grant all on sch_p000.view_buku_besar to group_darkogl_developer;

grant all privileges on all TABLES in schema sch_p000 to group_darkogl_developer;

alter table sch_dc_system.mhd_user_perusahaan drop constraint mhd_user_perusahaan_mhm_user_fk;

select
column_name::json->'key'
from schema_name.table_name;

nextval('sch_erp_inventory.seq_trh_penjualan'::regclass)

// JSON
SELECT
CAST(nilai1::jsonb->'padding' AS INTEGER) AS padding,
REPLACE(CAST(nilai1::jsonb->'prefix' AS CHARACTER VARYING), '"', '') AS prefix,
CAST(nilai1::jsonb->'date'->>'include' AS bool) AS incl_date,
CAST(nilai1::jsonb->'padding' AS integer) AS padding
FROM sch_p000.mhd_lookup a;


CREATE TABLE sch_p001.mhs_akun AS SELECT * FROM sch_p000.mhs_akun;
CREATE TABLE sch_p001.mhs_akun (LIKE sch_p000.mhs_akun INCLUDING all);

raise notice '% %', SQLERRM, SQLSTATE;