Kapan Menggunakan Stored Program

Sepuluh chapter terakhir melengkapi perangkat stored program kita: function bermacam bentuk hasil, exception yang tertangani, procedure pengelola transaksi, cursor kendali penuh. Perangkat yang lengkap mengundang godaan yang juga harus dijawab: haruskah semua logika Toko Maju Jaya pindah ke dalam database? Ini bukan pertanyaan teknis semata — ia menentukan siapa yang bisa mengubah aturan diskon (programmer database atau programmer aplikasi), di mana bug dilacak, dan seberapa mahal pindah kalau suatu hari Toko Maju Jaya berpindah dari PostgreSQL. Chapter penutup ini tidak mengajarkan sintaks baru, melainkan menimbang: kapan stored program jelas menang, kapan jelas kalah, dan bagaimana praktik modern menata keduanya — dengan demo nyata untuk klaim yang paling sering disebut orang: enkapsulasi privilege.

Argumen Pro: Apa yang Ditawarkan Stored Program #

Untuk logika yang berat datanya dan ringan keputusannya — agregat bertingkat, validasi lintas tabel, pembersihan massal — argumen pertama sering kali berdiri sendiri: tidak ada framework aplikasi yang menandingi “nol round trip”. Argumen keempat, enkapsulasi privilege, layak dibuktikan daripada dipercaya.

Argumen Kontra: Harga yang Dibayar #

Tabel kontra ini bukan alasan untuk menghindari stored program sama sekali — melainkan alasan untuk menempatkannya dengan selektif. Baca kedua tabel itu sebagai kontrak: pindahkan logika ke database hanya ketika kekuatan yang Anda kejar benar-benar dibutuhkan, dan Anda sanggup membayar biaya pemeliharaannya.

Kandidat Kuat: Pekerjaan yang Menempel pada Data #

Beberapa jenis pekerjaan hampir selalu lebih baik dikerjakan di dalam database: validasi lintas tabel yang harus konsisten apa pun jalur datanya, pekerjaan batch terjadwal (pengarsipan, agregat historis, pembersihan), pemicu otomatis yang berjalan tanpa campur tangan aplikasi (kelak dibahas di section Triggers dan Events), dan akses terkontrol ke data sensitif. Yang terakhir inilah yang kita buktikan sekarang: manajemen Toko Maju Jaya ingin kasir bisa melihat rata-rata gaji, tetapi tidak boleh melihat gaji per orang.

-- Enkapsulasi privilege: role kasir hanya boleh lewat function SECURITY DEFINER
-- (DROP IF EXISTS menjaga file tetap bisa diulang di cluster yang sama)
DROP ROLE IF EXISTS kasir;

CREATE ROLE kasir NOLOGIN;

CREATE TABLE gaji_karyawan (
    nama text PRIMARY KEY,
    gaji numeric(12,2) NOT NULL
);

INSERT INTO gaji_karyawan (nama, gaji) VALUES
    ('Budi Santoso', 8500000.00),
    ('Siti Rahayu',  7200000.00),
    ('Ani Kartika',  5500000.00);

REVOKE ALL ON gaji_karyawan FROM kasir;

CREATE FUNCTION rata_gaji()
RETURNS numeric
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    RETURN round(avg(gaji), 2) FROM gaji_karyawan;
END
$$;

GRANT EXECUTE ON FUNCTION rata_gaji() TO kasir;

Output yang Dihasilkan:

DROP ROLE
CREATE ROLE
CREATE TABLE
INSERT 0 3
REVOKE
CREATE FUNCTION
GRANT

Dua hal terjadi di file itu: role kasir dibuat lalu dicabut semua haknya atas gaji_karyawan, sementara function rata_gaji() dibuat dengan SECURITY DEFINER — function berjalan memakai hak pembuatnya (kita, pemilik tabel), bukan hak pemanggilnya. Hanya EXECUTE-nya saja yang diberikan ke kasir. Sekarang kita masuk ke sepatu kasir:

-- Sebagai kasir: rata-rata gaji boleh dibaca lewat function...
SET ROLE kasir;

SELECT rata_gaji() AS rata_rata;

RESET ROLE;

Output yang Dihasilkan:

SET
 rata_rata  
------------
 7066666.67
(1 row)

RESET

Dan percobaan membaca tabel mentahnya:

-- ...tetapi tabel gaji mentah tetap terkunci untuk role yang sama
SET ROLE kasir;

SELECT * FROM gaji_karyawan;

Output yang Dihasilkan:

psql:baca-langsung.sql:4: ERROR:  permission denied for table gaji_karyawan

Perbandingannya tajam: SELECT rata_gaji() mengembalikan 7066666.67 dengan mulus, sedangkan SELECT * FROM gaji_karyawan dihentikan server dengan permission denied for table gaji_karyawan. Kasir memperoleh tepat satu angka ringkasan; nama dan gaji per orang tetap tertutup. Pola ini — tabel terkunci rapat, pintu lewat function yang mengembalikan bentuk yang disetujui — adalah cara klasik stored program memperkuat keamanan tanpa memindahkan seluruh logika ke database.

Pekerjaan Batch adalah Warga Asli Database #

Kandidat kuat berikutnya: pekerjaan terjadwal yang membaca dan menulis dalam jumlah besar. Pengarsipan pesanan lama milik Toko Maju Jaya adalah contoh yang pas — di chapter Stored Procedure kita sudah melihat mengapa COMMIT per batch menyelamatkan kemajuan saat pekerjaan gagal di tengah:

-- Pekerjaan batch yang menempel pada data: pengarsipan pesanan lama
CREATE TABLE pesanan (
    id      integer PRIMARY KEY,
    tanggal date NOT NULL,
    total   numeric(12,2) NOT NULL
);

INSERT INTO pesanan (id, tanggal, total) VALUES
    (1, DATE '2026-01-05', 150000.00),
    (2, DATE '2026-01-12',  85000.00),
    (3, DATE '2026-02-03', 220000.00),
    (4, DATE '2026-03-15',  95000.00),
    (5, DATE '2026-04-20', 310000.00);

CREATE TABLE pesanan_arsip (
    id      integer PRIMARY KEY,
    tanggal date NOT NULL,
    total   numeric(12,2) NOT NULL
);

CREATE PROCEDURE arsipkan_pesanan_lama(batas date, ukuran_batch integer)
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
    v_no integer := 0;
BEGIN
    FOR rec IN SELECT id, tanggal, total FROM pesanan
                WHERE tanggal < batas ORDER BY id LOOP
        INSERT INTO pesanan_arsip VALUES (rec.id, rec.tanggal, rec.total);
        DELETE FROM pesanan WHERE id = rec.id;
        v_no := v_no + 1;

        IF v_no % ukuran_batch = 0 THEN
            COMMIT;
        END IF;
    END LOOP;

    COMMIT;
END
$$;

CALL arsipkan_pesanan_lama(DATE '2026-03-01', 2);

SELECT 'pesanan' AS tabel, count(*) AS baris FROM pesanan
UNION ALL
SELECT 'pesanan_arsip', count(*) FROM pesanan_arsip;

SELECT id, tanggal, total FROM pesanan_arsip ORDER BY id;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 5
CREATE TABLE
CREATE PROCEDURE
CALL
     tabel     | baris 
---------------+-------
 pesanan       |     2
 pesanan_arsip |     3
(2 rows)

 id |  tanggal   |   total   
----+------------+-----------
  1 | 2026-01-05 | 150000.00
  2 | 2026-01-12 |  85000.00
  3 | 2026-02-03 | 220000.00
(3 rows)

Prosedur arsipkan_pesanan_lama memindahkan tiga pesanan sebelum Maret 2026 ke tabel arsip, dua baris per batch yang di-COMMIT — semua perangkatnya sudah kita kuasai: loop record, procedure, transaksi per batch. Bayangkan versi aplikasinya: aplikasi harus SELECT satu batch, INSERT ke arsip, DELETE dari pesanan, COMMIT, lalu mengulang — di atas jaringan, dengan risiko terputus di tengah. Logika yang seluruh hidupnya mengurus baris-baris di satu tempat memang warga asli database.

Kandidat Lemah: Aturan Produk yang Sering Berubah #

Kebalikannya juga nyata. Aturan diskon bertingkat “gold 15%, silver 10%, biasa 5%” adalah aturan produk, bukan pekerjaan data. Aturan itu berubah karena keputusan bisnis — musim ramai, uji pasar, program loyalitas baru — dan setiap perubahan ingin lewat alur yang wajar di tim produk: branch Git, code review, unit test, staging, lalu rilis. Bila aturannya hidup di dalam function database, alur itu harus dibangun ulang secara terpisah untuk objek database: skrip deploy, hak akses CREATE FUNCTION, dan prosedur rollback database. Semakin sering aturan berubah, semakin mahal tempat itu.

Pertanyaan penentu kandidat lemah bukan “bisakah database melakukannya?” — hampir selalu bisa. Pertanyaannya: siapa yang mengubahnya, seberapa sering, dan lewat alur apa? Bila jawabannya “tim produk, dua minggu sekali, lewat code review”, tempatnya di kode aplikasi.

Thin Database vs Smart Database #

Perdebatan ini punya nama dan dua kubu yang sama-sama serius. Kubu thin database berpendapat database harus sekadar penyimpanan: tabel, index, constraint integritas — semua keputusan di aplikasi, sehingga logika mudah diuji, di-version, dan dipindah antar database. Kubu smart database berpendapat kekuatan penuh server — transaksi, locking, trigger, bahasa prosedural — justru dibeli untuk dipakai; logika dekat data lebih konsisten karena satu sumber kebenaran, dan semua klien (aplikasi utama, laporan, pekerjaan terjadwal) otomatis sepakat. Kedua kubu benar tentang hal yang berbeda: yang pertama benar tentang pemeliharaan aturan bisnis, yang kedua benar tentang konsistensi data.

Praktik yang matang biasanya berhenti di tengah, dengan garis pembatas yang jelas:

Praktik Modern: Migrasi Terkontrol + Stored Program Terpilih #

Praktik yang paling banyak diadopsi tim saat ini memadukan keduanya. Skema dan kode SQL hidup di repositori aplikasi lewat database migration — berkas SQL terurut dan ter-version-control yang dijalankan oleh tool migrasi (seperti yang dibahas nanti di chapter Database Migrations ). Function, procedure, dan trigger tetap ditulis dan diuji seperti biasa, tetapi perubahan pada objek database mengalir lewat migrasi yang sama dengan perubahan tabel: sekali commit, sekali review, sekali deploy. Dengan begitu, biaya “version control canggung” dari tabel kontra tadi dibayar lunas, dan yang tersisa di database hanyalah pekerjaan yang memang paling bagus dikerjakan di sana.

Dua kebiasaan menutup praktik ini. Pertama, uji function dan procedure secara eksplisit — minimal rangkaian SELECT/CALL dengan hasil yang diperiksa di migration test; tim yang lebih disiplin memakai framework pengujian khusus database seperti pgTAP agar aturan di database mendapat unit test sungguhan seperti kode aplikasi. Kedua, dokumentasikan kepemilikan: setiap stored program yang hidup di produksi harus jelas siapa pemiliknya dan mengapa ia di sana — supaya tidak menjadi logika yatim yang tak berani disentuh siapa pun.

Kita menutup section Stored Programs dengan perangkat lengkap dan rambu penggunaannya. Satu jenis stored program masih belum kita sentuh — yang tidak menunggu dipanggil SELECT atau CALL, melainkan menunggu peristiwa pada tabel: INSERT, UPDATE, DELETE. Ketika validasi atau catatan audit harus berjalan otomatis setiap kali data berubah, itulah pekerjaan trigger — pintu masuk section berikutnya, Triggers dan Events .

Referensi #

• PL/pgSQL Overview: Advantages and Limitations
• CREATE FUNCTION Reference
• CREATE PROCEDURE Reference

-- Sebagai kasir: rata-rata gaji boleh dibaca lewat function...
SET ROLE kasir;

SELECT rata_gaji() AS rata_rata;

RESET ROLE;

-- ...tetapi tabel gaji mentah tetap terkunci untuk role yang sama
SET ROLE kasir;

SELECT * FROM gaji_karyawan;

-- Enkapsulasi privilege: role kasir hanya boleh lewat function SECURITY DEFINER
-- (DROP IF EXISTS menjaga file tetap bisa diulang di cluster yang sama)
DROP ROLE IF EXISTS kasir;

CREATE ROLE kasir NOLOGIN;

CREATE TABLE gaji_karyawan (
    nama text PRIMARY KEY,
    gaji numeric(12,2) NOT NULL
);

INSERT INTO gaji_karyawan (nama, gaji) VALUES
    ('Budi Santoso', 8500000.00),
    ('Siti Rahayu',  7200000.00),
    ('Ani Kartika',  5500000.00);

REVOKE ALL ON gaji_karyawan FROM kasir;

CREATE FUNCTION rata_gaji()
RETURNS numeric
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    RETURN round(avg(gaji), 2) FROM gaji_karyawan;
END
$$;

GRANT EXECUTE ON FUNCTION rata_gaji() TO kasir;

-- Pekerjaan batch yang menempel pada data: pengarsipan pesanan lama
CREATE TABLE pesanan (
    id      integer PRIMARY KEY,
    tanggal date NOT NULL,
    total   numeric(12,2) NOT NULL
);

INSERT INTO pesanan (id, tanggal, total) VALUES
    (1, DATE '2026-01-05', 150000.00),
    (2, DATE '2026-01-12',  85000.00),
    (3, DATE '2026-02-03', 220000.00),
    (4, DATE '2026-03-15',  95000.00),
    (5, DATE '2026-04-20', 310000.00);

CREATE TABLE pesanan_arsip (
    id      integer PRIMARY KEY,
    tanggal date NOT NULL,
    total   numeric(12,2) NOT NULL
);

CREATE PROCEDURE arsipkan_pesanan_lama(batas date, ukuran_batch integer)
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
    v_no integer := 0;
BEGIN
    FOR rec IN SELECT id, tanggal, total FROM pesanan
                WHERE tanggal < batas ORDER BY id LOOP
        INSERT INTO pesanan_arsip VALUES (rec.id, rec.tanggal, rec.total);
        DELETE FROM pesanan WHERE id = rec.id;
        v_no := v_no + 1;

        IF v_no % ukuran_batch = 0 THEN
            COMMIT;
        END IF;
    END LOOP;

    COMMIT;
END
$$;

CALL arsipkan_pesanan_lama(DATE '2026-03-01', 2);

SELECT 'pesanan' AS tabel, count(*) AS baris FROM pesanan
UNION ALL
SELECT 'pesanan_arsip', count(*) FROM pesanan_arsip;

SELECT id, tanggal, total FROM pesanan_arsip ORDER BY id;