Belajar SQL PostgreSQL - Stored Procedures, Functions (PL/pgSQL) & Triggers

#Pendahuluan

Selamat datang di episode 13 series Belajar SQL PostgreSQL! Sejauh ini, semua logika ada di aplikasi dan database hanya "gudang data". Tapi ada kalanya lebih baik menaruh logika di dalam database itu sendiri: validasi yang harus dijamin pasti, otomatisasi yang tidak boleh dilupakan aplikasi mana pun, atau kalkulasi kompleks yang terlalu mahal dikirim balik-pergi ke aplikasi.

Di episode ini, kita akan membahas tiga alat utama untuk "coding di dalam database": User-Defined Functions (UDF) menggunakan bahasa internal PostgreSQL bernama PL/pgSQL, stored procedures beserta perbedaannya dengan function (terutama soal transaksi), dan database triggers untuk otomatisasi — dengan dua studi kasus klasik: kolom updated_at otomatis dan audit logging.

#User-Defined Functions (UDF) dengan PL/pgSQL

Function di PostgreSQL adalah blok logika yang menerima parameter, memproses, dan mengembalikan nilai. Bahasa penulisannya bisa berbagai macam (SQL, Python, C), tapi yang paling umum dan native adalah PL/pgSQL.

#Sintaks Dasar Function

CREATE FUNCTION tambah(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

Panggil dengan SELECT tambah(3, 4) dan hasilnya 7. Perhatikan struktur: LANGUAGE plpgsql menentukan bahasa, dan isi body diapit $$ ... $$ (dollar quoting) yang menggantikan tanda kutip tunggal agar aman menulis string di dalamnya.

#Function dengan Logika Lebih Kompleks

Function bisa berisi variabel, percabangan, dan query:

CREATE FUNCTION cek_stok(pid UUID, qty INTEGER)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
    stok_tersedia INTEGER;
BEGIN
    SELECT stock INTO stok_tersedia
    FROM inventory
    WHERE product_id = pid;
 
    IF stok_tersedia IS NULL THEN
        RETURN FALSE;
    ELSIF stok_tersedia >= qty THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;
$$;

DECLARE mendeklarasikan variabel, SELECT ... INTO mengisi variabel dari query, dan blok IF/ELSIF/ELSE menangani logika. Function inilah yang akan kita pakai saat studi kasus e-commerce di episode 20.

#Stored Procedures: Function yang Bisa Transaksi

Perbedaan kunci antara function dan stored procedure di PostgreSQL:

Perbedaan transaksi inilah yang paling fundamental. Function berjalan di dalam transaksi pemanggilnya dan tidak bisa mengontrolnya. Procedure bisa menjalankan COMMIT atau ROLLBACK di dalam body-nya — berguna untuk alur yang harus menyimpan hasil sebagian:

CREATE PROCEDURE proses_order(pid UUID)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders SET status = 'processing' WHERE id = pid;
    COMMIT;
END;
$$;

Panggil procedure dengan perintah CALL:

CALL proses_order('1c2d3e4f-0000-0000-0000-000000000001');

#Database Triggers: Otomatisasi Eksekusi

Trigger adalah fungsi yang dijalankan secara otomatis ketika event tertentu terjadi pada tabel. Event-nya bisa INSERT, UPDATE, DELETE, atau TRUNCATE, dan waktunya bisa BEFORE (sebelum operasi) atau AFTER (sesudah operasi), per baris (FOR EACH ROW) atau per statement (FOR EACH STATEMENT).

#Studi Kasus 1: Kolom updated_at Otomatis

Masalah klasik: aplikasi lupa memperbarui updated_at. Trigger menyelesaikannya sekali untuk selamanya:

CREATE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;
 
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Dengan trigger ini, setiap UPDATE pada tabel users otomatis mengisi updated_at dengan waktu sekarang — tidak peduli siapa yang mengeksekusi, dari aplikasi mana, atau lewat query apa. NEW adalah baris baru (hasil update) yang bisa kita modifikasi sebelum disimpan.

#Studi Kasus 2: Audit Logging

Pola kedua yang sangat berharga: mencatat setiap perubahan data ke tabel audit. Ini jejak yang diperlukan untuk kepatuhan, debugging, dan forensik:

CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name TEXT NOT NULL,
    action TEXT NOT NULL,
    row_id UUID NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE FUNCTION log_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, row_id)
    VALUES (TG_TABLE_NAME, TG_OP, NEW.id);
    RETURN NEW;
END;
$$;
 
CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_audit();

Fungsi trigger memakai variabel khusus: TG_TABLE_NAME (nama tabel), TG_OP (operasi: INSERT/UPDATE/DELETE). Untuk trigger DELETE, NEW tidak ada — gunakan OLD untuk mengambil id baris lama.

#Trigger dan Constraint

Penting untuk menempatkan tanggung jawab dengan benar: constraint menangani validasi sederhana yang statis (CHECK, FOREIGN KEY, UNIQUE), sedangkan trigger menangani logika dinamis yang butuh konteks lebih luas — seperti membaca tabel lain, atau memodifikasi baris lain sebagai efek samping. Jangan membuat trigger BEFORE INSERT untuk validasi yang sebenarnya bisa jadi CHECK — lebih lambat dan lebih mudah bocor.

#Penutup

Inti yang harus dibawa pulang:

• Function mengembalikan nilai dan bisa dipanggil di query; procedure dipanggil dengan CALL dan bisa mengontrol transaksi.
• PL/pgSQL menambahkan variabel, percabangan, dan loop di atas SQL.
• Trigger otomatis menjalankan fungsi pada event DML (BEFORE/AFTER, FOR EACH ROW).
• Trigger updated_at dan audit log adalah pola wajib untuk database production.
• Trigger menambah biaya setiap operasi — pakai secukupnya dan dokumentasikan.

Di episode 14 selanjutnya, kita masuk ke performa: Deep Dive Indexing Strategies — mulai dari mengapa indexing mengubah pencarian dari O(N) ke O(log N), tipe-tipe index B-Tree, Hash, GIN, dan BRIN, hingga teknik lanjutan seperti partial index, expression index, dan composite index.