Stored procedure

Topik ini membahas stored procedure, yaitu prosedur tersimpan yang mirip fungsi tetapi dipanggil dengan CALL dan dibedakan oleh satu kemampuan penting, yaitu kendali transaksi di dalam isinya. Sejak PostgreSQL 11, prosedur menjadi tempat yang tepat untuk logika yang berisi beberapa unit kerja sekaligus.

Blok penyiapan dataset#

# siapkan ulang tabel dan data contoh untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori, arsip_pinjam CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
SQL

Prosedur pertama dan pemanggilannya#

CREATE PROCEDURE menyerupai CREATE FUNCTION tanpa jenis kembalian wajib, dan pemanggilannya melalui CALL, bukan di dalam SELECT. Blok berikut membuat prosedur yang menandai satu peminjaman selesai lalu mencatat ringkasannya ke tabel arsip.

# prosedur penyelesaian peminjaman beserta arsipnya
psql -U postgres -d perpustakaan <<'SQL'
CREATE TABLE arsip_pinjam (peminjaman_id INTEGER PRIMARY KEY, ditutup DATE);
CREATE PROCEDURE tutup_pinjaman(p_id INTEGER, p_hari DATE)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE peminjaman SET tanggal_kembali = p_hari WHERE id = p_id;
    INSERT INTO arsip_pinjam (peminjaman_id, ditutup) VALUES (p_id, p_hari)
    ON CONFLICT (peminjaman_id) DO UPDATE SET ditutup = EXCLUDED.ditutup;
END;
$$;
CALL tutup_pinjaman(3, DATE '2025-04-01');
SELECT p.id, p.tanggal_kembali, a.ditutup FROM peminjaman p JOIN arsip_pinjam a ON a.peminjaman_id = p.id WHERE p.id = 3;
DROP PROCEDURE tutup_pinjaman(INTEGER, DATE);
DROP TABLE arsip_pinjam;
SQL

CREATE TABLE
CREATE PROCEDURE
CALL
 id | tanggal_kembali |  ditutup
----+-----------------+------------
  3 | 2025-04-01      | 2025-04-01
(1 row)

DROP PROCEDURE
DROP TABLE

CALL menjalankan kedua langkahnya dalam satu pemanggilan, dan keduanya tunduk pada transaksi yang membungkusnya, sebagaimana fungsi. Hingga titik ini keduanya tampak identik, dan pembeda yang sesungguhnya dibahas pada bagian berikutnya.

Kendali transaksi di dalam prosedur#

Prosedur dapat membuka, menetapkan, dan membatalkan transaksi di dalam isinya melalui COMMIT dan ROLLBACK, sedangkan fungsi tidak boleh, karena fungsi berjalan di dalam transaksi pemanggilnya. Kemampuan ini menjadikan prosedur tempat logika batch yang menetapkan progres per kelompok alih-alih menumpuk seluruhnya dalam satu transaksi raksasa.

# prosedur batch dengan penetapan per kelompok
psql -U postgres -d perpustakaan <<'SQL'
CREATE TABLE arsip_pinjam (peminjaman_id INTEGER PRIMARY KEY, ditutup DATE);
CREATE PROCEDURE tutup_berkelompok(p_hari DATE)
LANGUAGE plpgsql
AS $$
DECLARE
    v_id INTEGER;
BEGIN
    FOR v_id IN SELECT id FROM peminjaman WHERE tanggal_kembali IS NULL AND id <= 5 ORDER BY id LOOP
        UPDATE peminjaman SET tanggal_kembali = p_hari WHERE id = v_id;
        INSERT INTO arsip_pinjam (peminjaman_id, ditutup) VALUES (v_id, p_hari);
        COMMIT;
    END LOOP;
END;
$$;
CALL tutup_berkelompok(DATE '2025-04-05');
SELECT COUNT(*) AS tertutup FROM peminjaman WHERE tanggal_kembali IS NOT NULL AND id <= 5;
DROP PROCEDURE tutup_berkelompok(DATE);
DROP TABLE arsip_pinjam;
SQL

CREATE TABLE
CREATE PROCEDURE
CALL
 tertutup
----------
        5
(1 row)

DROP PROCEDURE
DROP TABLE

Prosedur menutup tiga peminjaman pertama yang masih terbuka, dengan COMMIT per putaran, sehingga kegagalan di tengah prosedur meninggalkan kelompok yang sudah ditetapkan alih-alih kehilangan semuanya. Perbandingan dengan satu transaksi besar adalah pertukaran yang disengaja, yaitu progres yang bertahan dibayar dengan keutuhan yang dilepaskan.

Memilih antara fungsi dan prosedur#

Pilihan antara keduanya mengikuti bentuk pemanggilannya. Fungsi dipanggil di dalam query dan wajib mengembalikan nilai, sehingga cocok untuk perhitungan yang melengkapi SELECT, sedangkan prosedur dipanggil berdiri sendiri dan menjadi tempat aksi yang meliputi beberapa pernyataan.

Tabel ini merangkum pembagiannya, dan aturan praktisnya ringkas, yaitu apa yang diperlukan SQL dipanggil sebagai fungsi, sedangkan apa yang diperlukan aplikasi sebagai aksi dipanggil sebagai prosedur.

Kesalahan umum#

Kesalahan pertama adalah mengira prosedur mewarisi kemampuan fungsi di dalam SELECT, padahal pemanggilannya CALL dan tidak dapat ditanam ke dalam query. Kesalahan kedua adalah COMMIT di dalam prosedur yang dipanggil dari dalam transaksi eksplisit milik pemanggil, yang ditolak server karena penetapan menabrak unit kerja di atasnya.

Kesalahan ketiga adalah memilih prosedur untuk perhitungan sederhana yang seharusnya fungsi sql, sehingga query kehilangan kemampuan penataan ulang perencana. Kesalahan keempat adalah loop prosedur yang menirukan pekerjaan query tunggal, karena pembaruan set-based dengan satu UPDATE tetap lebih cepat, dan prosedur dipakai ketika langkah per butir memang dituntut logikanya.

Prosedur dan fungsinya dapat diaudit tanpa membuka definisi satu per satu, karena katalog pg_proc memuat nama, argumen, dan sumber seluruhnya, sedangkan pg_depend memetakan ketergantungan antar objek. Kebiasaan audit yang murah mencari prosedur yang memakai COMMIT untuk memeriksa pola penetapan per kelompok, dan mencari fungsi VOLATILE yang seharusnya STABLE untuk menemukan janji yang belum dinyatakan. Dua pencarian itu sering menemukan perbaikan performa yang murah sebelum keluhannya muncul.

Ringkasan#

• CREATE PROCEDURE mengemas aksi multi-pernyataan yang dipanggil dengan CALL, berbeda dari fungsi yang hidup di dalam query.
• Prosedur boleh menjalankan COMMIT dan ROLLBACK di dalamnya, kemampuan yang tidak dimiliki fungsi.
• Pola batch dengan penetapan per kelompok menukar keutuhan dengan progres yang bertahan, dan pilihannya disengaja.
• Fungsi untuk perhitungan yang diminta SQL, prosedur untuk aksi yang diminta aplikasi dan pemeliharaan.

Referensi#

• CREATE PROCEDURE — PostgreSQL Documentation — Referensi prosedur beserta kendali transaksinya.
• CALL — PostgreSQL Documentation — Referensi pemanggilan prosedur.
• Transaction Management in Procedures — PostgreSQL Documentation — Aturan COMMIT dan ROLLBACK di dalam plpgsql.