KAL
Menu

Fungsi

Menulis fungsi plpgsql pertama beserta pilihan bahasa fungsinya.

Daftar isi

Topik ini membahas fungsi tersimpan, yaitu logika yang dideklarasikan di server dan dipanggil dari SQL, dengan plpgsql sebagai bahasa utamanya. Fungsi menerima argumen, menjalankan pernyataan, dan mengembalikan nilai, sehingga perhitungan yang berulang tinggal dipanggil namanya di mana pun.

Blok penyiapan dataset#

bash 
# 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, denda_uji 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

Fungsi pertama dalam plpgsql#

CREATE FUNCTION menyatakan nama, argumen beserta tipenya, dan jenis kembalinya, sedangkan isinya ditulis dalam bahasa yang dinyatakan pada LANGUAGE. Blok berikut membuat fungsi yang menghitung denda peminjaman terlambat, yaitu seribu per hari melewati tenggat tiga puluh hari.

bash 
# fungsi plpgsql pertama: hitung denda
psql -U postgres -d perpustakaan <<'SQL'
CREATE FUNCTION hitung_denda(p_id INTEGER, p_hari_ini DATE)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN GREATEST(
        0,
        (COALESCE(p_hari_ini, CURRENT_DATE) - (tanggal_pinjam + 30))::INTEGER
    ) * 1000
    FROM peminjaman WHERE id = p_id;
END;
$$;
SELECT id, hitung_denda(id, DATE '2025-04-01') AS denda FROM peminjaman WHERE tanggal_kembali IS NULL ORDER BY id;
DROP FUNCTION hitung_denda(INTEGER, DATE);
SQL
text Keluaran 
CREATE FUNCTION
 id | denda
----+-------
  3 |     0
  5 |     0
  6 |     0
  8 |     0
(4 rows)

DROP FUNCTION

Isi fungsi dibaca dari dalam ke luar, yaitu selisih hari antara hari acuan dan tenggat, dijepit nol oleh GREATEST, lalu dikalikan tarifnya. Penamaan argumen dengan awalan p_ menjaga parameter tidak bertabrakan dengan nama kolom, dan fungsi dipanggil persis seperti fungsi bawaan di dalam SELECT.

Variabel, percabangan, dan pengembalian#

plpgsql menyediakan deklarasi variabel pada blok DECLARE, percabangan IF dan CASE, serta perulangan, dan fungsi yang cukup biasanya memakai ketiganya. Blok berikut menilai status peminjaman dengan IF yang bertingkat.

bash 
# fungsi dengan variabel dan percabangan
psql -U postgres -d perpustakaan <<'SQL'
CREATE FUNCTION status_pinjam(p_id INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_tenggat DATE;
BEGIN
    SELECT tanggal_pinjam + 30 INTO v_tenggat FROM peminjaman WHERE id = p_id;
    IF v_tenggat IS NULL THEN
        RETURN 'tidak ada';
    ELSIF v_tenggat < DATE '2025-04-01' THEN
        RETURN 'terlambat';
    ELSE
        RETURN 'lancar';
    END IF;
END;
$$;
SELECT id, status_pinjam(id) AS status FROM peminjaman WHERE id IN (1, 3) ORDER BY id;
DROP FUNCTION status_pinjam(INTEGER);
SQL
text Keluaran 
CREATE FUNCTION
 id |  status
----+-----------
  1 | terlambat
  3 | lancar
(2 rows)

DROP FUNCTION

Kata INTO menampung hasil SELECT ke variabel, dan tiga jalur IF-nya menilai ketiadaan baris, keterlambatan, serta kelancaran. Fungsi yang tumbuh lebih panjang dipindahkan sebagian menjadi query biasa bila memungkinkan, karena setiap baris plpgsql adalah logika yang tidak dapat ditelusuri oleh perencana.

Pilihan bahasa dan sifat fungsi#

PostgreSQL menerima beberapa bahasa untuk fungsi, dan pilihan yang praktis adalah sql untuk satu pernyataan yang dapat ditanam (inline) oleh perencana, plpgsql untuk logika bertingkat, dan bahasa eksternal yang terpasang sebagai ekstensi. Kata STABLE dan VOLATILE menyatakan janji fungsinya terhadap keadaan basis data, dan janji yang tepat membuka jalan penataan ulang oleh perencana, misalnya fungsi STABLE yang hanya dibaca sekali per query.

bash 
# fungsi sql yang ditanam perencana
psql -U postgres -d perpustakaan <<'SQL'
CREATE FUNCTION tahun_pertengahan(p_a INTEGER, p_b INTEGER)
RETURNS INTEGER
LANGUAGE sql
IMMUTABLE
RETURN (p_a + p_b) / 2;
SELECT tahun_pertengahan(2018, 2023) AS tengah;
DROP FUNCTION tahun_pertengahan(INTEGER, INTEGER);
SQL
text Keluaran 
CREATE FUNCTION
 tengah
--------
   2020
(1 row)

DROP FUNCTION

Fungsi sql dengan ekspresi tunggal ditulis tanpa blok BEGIN, dan sifat IMMUTABLE-nya menjanjikan hasil yang sama untuk masukan yang sama, janji yang sah untuk aritmetika murni ini. Janji yang keliru, misalnya IMMUTABLE atas fungsi yang membaca tabel, menghasilkan rencana yang salah, sehingga sifatnya dinyatakan dengan kehati-hatian.

Kesalahan umum#

Kesalahan pertama adalah menaruh logika berat ke dalam fungsi yang dipanggil per baris, pola yang melipatgandakan biayanya sebagaimana anti-pola N+1 pada modul index, dan fungsinya dipanggil sekali per query atau ditanam sebagai fungsi sql. Kesalahan kedua adalah sifat VOLATILE pada fungsi yang sebenarnya stabil, yang menutup jalan penataan ulang dan melambatkan query tanpa alasan.

Kesalahan ketiga adalah menangkap error di dalam fungsi lalu menyembunyikannya dengan mengembalikan NULL, kebiasaan yang menyembunyikan data yang rusak, dan penanganan error yang disengaja memakai klausa EXCEPTION dengan tindakan yang nyata. Kesalahan keempat adalah merujuk nama kolom yang bertabrakan dengan nama argumen, yang dibaca server sebagai rujukan kolom, dan awalan p_ pada argumen menyelesaikannya sejak awal.

Argumen keluaran memberi fungsi cara mengembalikan beberapa nilai tanpa jenis komposit, dengan kata OUT pada deklarasi argumennya, dan pemanggilannya tetap satu baris di dalam SELECT. Penamaan argumen juga membuka pemanggilan bernama melalui nama => nilai, keterbacaan yang berarti pada fungsi dengan banyak argumen bertipe sama. Kedua bentuk ini membuat tanda tangan fungsi menjadi antarmuka yang terdokumentasi sendiri, dan perubahannya membutuhkan migration sebagaimana perubahan skema lainnya.

Ringkasan#

  • CREATE FUNCTION menyatakan argumen, jenis kembalian, dan bahasanya, dengan plpgsql untuk logika bertingkat.
  • Variabel dideklarasikan pada DECLARE, diisi melalui SELECT INTO, dan dinilai IF atau CASE sebagaimana bahasa pemrograman.
  • Bahasa sql dengan ekspresi tunggal dapat ditanam perencana, dan sifat IMMUTABLE, STABLE, serta VOLATILE adalah janji yang dinyatakan hati-hati.
  • Fungsi yang dipanggil per baris menumpuk biaya, sehingga fungsinya dipanggil sekali per query atau dihindari.

Referensi#