WHAT'S NEW?

Belajar PostgreSQL: Audit Data Tabel - Part 4

Dalam sebuah aplikasi yg kompleks, audit adalah hal wajib dimiliki karena segala proses data harus di cek utk menghindari kesalahan ataupun utk tujuan tertentu. Misalkan kita ingin mengetahui siapa, apa dan kapan dilakukannya sesuatu terhadap data dalam sistem, salah satu cara utk melakukannya adalah dengan membuat log data pada tabel tertentu.

Kita akan membahas mengenai proses audit dengan menggunakan PostgreSQL. Pada kasus ini setidaknya ada 2 cara yg dapat dilakukan utk proses audit data:

- Audit data dengan trigger

- Memberikan akses tabel hanya melalui sebuah fungsi dan melakukan audit dari fungsi tersebut

Kita coba buat contoh terkait kedua cara diatas. Pertama kita buat sebuah tabel terlebih dahulu.

CREATE TABLE tb_penggajian(
  nama_pegawai text PRIMARY KEY,
  nominal_gaji integer NOT NULL
);

CREATE TABLE tb_log_gaji(
  diganti_oleh text DEFAULT CURRENT_USER,
  waktu_ganti timestamp DEFAULT CURRENT_TIMESTAMP,
  jenis_input text,
  nama_pegawai text,
  gaji_lama integer,
  gaji_baru integer
);

REVOKE ALL ON tb_log_gaji FROM PUBLIC;
GRANT ALL ON tb_log_gaji TO managers;

Pada 2 query terakhir diatas, dimisalkan kita membuat hak akses dimana user biasa tidak seharusnya dapat mengakses tabel log utk audit dan hanya bisa diakses oleh manager. Lalu kita akan buat fungsi utk proses mencatat ataupun membuat log utk transaksi yg dilakukan.

CREATE OR REPLACE FUNCTION log_perubahan_gaji () RETURNS TRIGGER AS
$$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO tb_log_gaji(jenis_input, nama_pegawai, gaji_baru)
    VALUES (TG_OP, NEW.nama_pegawai, NEW.nominal_gaji);
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO tb_log_gaji(jenis_input, nama_pegawai, gaji_lama, gaji_baru)
    VALUES (TG_OP, NEW.nama_pegawai, OLD.nominal_gaji, NEW.nominal_gaji);
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO tb_log_gaji(jenis_input, nama_pegawai, gaji_lama)
    VALUES (TG_OP, NEW.nama_pegawai, OLD.nominal_gaji);
  END IF;

  RETURN NEW;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_audit_perubahan_gaji
AFTER INSERT OR UPDATE OR DELETE ON tb_penggajian
FOR EACH ROW EXECUTE PROCEDURE log_perubahan_gaji();

Selanjutnya, kita buat contoh data tb_penggajian utk mencoba fungsi trigger yg telah kita buat.

INSERT INTO tb_penggajian values('John', 1500000);
UPDATE tb_penggajian set nominal_gaji = 1700000 where nama_pegawai = 'John';
INSERT INTO tb_penggajian values('Jenny', 1500000);
UPDATE tb_penggajian set nominal_gaji = nominal_gaji + 200000;
SELECT * FROM tb_penggajian;
nama_pegawai | nominal_gaji
----------+---------
John | 1900000
Jenny | 1700000
(2 rows)

Dari query diatas kita sudah melakukan proses insert dan update data, tentunya hal ini sudah di record kedalam tabel log yg sudah kita buat. Jika kita cek tabel log maka akan tampil log data.

Kita sudah dapatkan hasil log yg sesuai. Sedikit tambahan sekiranya kita tidak ingin ada user lain yg dapat mengakses tabel tb_penggajian, kita dapat mengatur hak aksesnya dengan query berikut:

REVOKE ALL ON tb_penggajian FROM PUBLIC;

Dan juga kita dapat memberikan akses user hanya pada 2 macam fungsi, misalnya utk setiap user hanya dapat melihat data tabel tb_penggajian dan setiap user lainnya dapat merubah data tabel tb_penggajian, yaitu seorang manager.

Fungsi yg telah kita buat tadi memiliki klausa SECURITY DEFINER yg berarti fungsi tersebut hanya akan dijalankan melalui user yg membuatnya.

Kita lanjutkan dengan membuat fungsi mencari gaji.

CREATE OR REPLACE FUNCTION cari_gaji(text)
RETURNS INTEGER AS
$$
  -- Jika anda melihat gaji orang lain, maka akan dicatat
  INSERT INTO tb_log_gaji(jenis_input, nama_pegawai, gaji_baru)
  SELECT 'SELECT', nama_pegawai, nominal_gaji FROM tb_penggajian
  WHERE upper(nama_pegawai) = upper($1)
  AND upper(nama_pegawai) != upper(CURRENT_USER); -- Jangan catat utk gaji sendiri
  -- tampilkan gaji yg diinginkan
  SELECT nominal_gaji FROM tb_penggajian
  WHERE upper(nama_pegawai) = upper($1);
$$
LANGUAGE SQL SECURITY DEFINER;

Fungsi diatas merupakan contoh sederhana yg mana kita dapat melihat gaji orang lain dan tentunya hal ini harus dilakukan atas tanggungjawab pekerjaan, ya kasus seperti ini hanya diperuntukkan kepada orang tertentu semisal seorang manager. Dan juga bila dilihat kembali fungsi diatas kita buat hanya menggunakan bahasa SQL dan bukan menggunakan PL/pgSQL, karena memang PostgreSQL mendukung banyak jenis bahasa pemrograman utk membuat sebuah fungsi.

Kita lanjutkan dengan fungsi terakhir yaitu mengatur gaji (set nominal_gaji). Fungsi yg akan kita buat ini berfungsi utk memeriksa apakah pegawai ditemukan dalam data, jika tidak ada maka akan dibuat secara otomatis. Ketika mengatur gaji pegawai menjadi 0, makan pegawai tersebut akan dihapus datanya dari tabel tb_penggajian.

CREATE OR REPLACE FUNCTION atur_penggajian(input_nama_pegawai text, input_nominal_gaji int)
RETURNS TEXT AS
$$
DECLARE
  gaji_lama integer;
BEGIN
  SELECT nominal_gaji INTO gaji_lama
  FROM tb_penggajian
  WHERE upper(nama_pegawai) = upper(input_nama_pegawai);

  IF NOT FOUND THEN
    INSERT INTO tb_penggajian VALUES(input_nama_pegawai, input_nominal_gaji);
    INSERT INTO tb_log_gaji(jenis_input,nama_pegawai,gaji_baru)
    VALUES ('INSERT',input_nama_pegawai,input_nominal_gaji);
    RETURN 'INSERTED USER ' || input_nama_pegawai;
  ELSIF input_nominal_gaji > 0 THEN
    UPDATE tb_penggajian SET nominal_gaji = input_nominal_gaji
    WHERE upper(nama_pegawai) = upper(input_nama_pegawai);
    INSERT INTO tb_log_gaji (jenis_input,nama_pegawai,gaji_lama,gaji_baru)
    VALUES ('UPDATE',input_nama_pegawai,gaji_lama,input_nominal_gaji);
    RETURN 'UPDATED USER ' || input_nama_pegawai;
  ELSE -- set gaji 0
    DELETE FROM tb_penggajian
    WHERE upper(nama_pegawai) = upper(input_nama_pegawai);
    INSERT INTO tb_log_gaji (jenis_input,nama_pegawai,gaji_lama)
    VALUES ('DELETE',input_nama_pegawai,gaji_lama);
    RETURN 'DELETED USER ' || input_nama_pegawai;
  END IF;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

Sekarang kita perlu utk menghapus trigger yg telah kita buat karena kita sudah tidak membutuhkannya lagi (jika tidak dihapus maka data log akan tersimpan 2x) dan kita akan menggunakan fungsi baru yg kita buat diatas.

Data yg kita dapatkan sangat rapi bukan, memang diawal proses terlihat sangat sulit (meskipun sebenarnya memang sulit sih :D ) tapi utk jangka panjang akan sangat bermanfaat. Sedikit tambahan pada data diatas nama pegawai yg kita miliki tidak memiliki konsistensi terhadap inputan, jadi kita bisa input data dengan huruf kapital maupun tidak. Maka dari itu konsistensi data sangatlah diperlukan dan utk kasus begini akan lebih baik kita simpan dalam bentuk huruf kapital semua. Caranya cukup mudah hanya dengan menambahkan constraint CHECK seperti yg sudah pernah kita pakai di part sebelumnya.

CHECK (nama_pegawai = upper(nama_pegawai))

Tetapi kita akan menggunakan trigger utk mengatasi hal ini. Mari kita langsung buat trigger nya.

CREATE OR REPLACE FUNCTION uppercase_name() RETURNS TRIGGER AS
$$
BEGIN
NEW.nama_pegawai = upper(NEW.nama_pegawai);
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER uppercase_nama_pegawai
BEFORE INSERT OR UPDATE OR DELETE ON tb_penggajian
FOR EACH ROW
EXECUTE PROCEDURE uppercase_name();

Saat fungsi atur_penggajian() digunakan kembali nantinya maka semua nama pegawai yg diinput akan disimpan dalam huruf kapital.

Lalu utk mengatasi nama pegawai yg sebelumnya kita cukup meng-update datanya dan menambahkan constraint CHECK.

UPDATE tb_penggajian SET nama_pegawai = upper(nama_pegawai)
WHERE NOT
nama_pegawai = upper(nama_pegawai);

ALTER TABLE tb_penggajian ADD CONSTRAINT nama_pegawai_must_be_uppercase
CHECK (nama_pegawai = upper(nama_pegawai));

Itulah beberapa contoh kasus dalam penerapan sistem audit dengan PostgreSQL. Semua contoh diatas hanyalah utk kasus sederhana, tetapi setidaknya bisa dimengerti utk penerapan pada kasus aplikasi yg nyata. Jangan lupa selalu isi kolom komentar jika ingin bertanya dan koreksi yg harus saya dilakukan.

Belajar PostgreSQL: Penanganan Data Dengan Trigger - Part 3

Mungkin disini bagian yg sudah bisa saya anggap familiar ketika berhubungan dengan trigger. Ya, sebuah fungsi yang secara otomatis akan dieksekusi dengan ketentuan yang sudah disesuaikan, inilah yg dikenal dengan istilah Trigger dalam sebuah database server.

Mari kita langsung ke praktek, kita buat sebuah tabel baru sbb:

CREATE TABLE tb_stok_buah (
   nama_buah text PRIMARY KEY,
   stok_buah integer NOT NULL,
   jml_pemesanan integer NOT NULL DEFAULT 0,
   CHECK (stok_buah between 0 and 1000 ),
   CHECK (jml_pemesanan <= stok_buah)
);

Kegunaan CHECK disini adalah utk memeriksa berdasarkan ketentuan yg diinginkan, jadi pada contoh tabel diatas kita tidak bisa memiliki stok buah lebih dari 1000 dan tidak bisa memiliki nilai negatif serta jumlah pemesanan tidak bisa lebih dari stok. Kita lanjutkan dengan tabel kedua.

CREATE TABLE tb_penawaran_buah (
   id_penawaran serial PRIMARY KEY,
   nama_penerima text,
   tgl_penawaran timestamp default current_timestamp,
   nama_buah text REFERENCES tb_stok_buah,
   jml_penawaran integer
);

Kemudian kita buatkan sebuah fungsi trigger utk proses pemesanan. Utk membuatnya kita terlebih dahulu mebuat sebuah fungsi yg mirip seperti contoh pada artikel sebelumnya hanya saja nilai return adalah berupa trigger.

CREATE OR REPLACE FUNCTION pemesanan_buah () RETURNS trigger AS
$$
BEGIN
   IF TG_OP = 'INSERT' THEN
    UPDATE tb_stok_buah SET jml_pemesanan = jml_pemesanan + NEW.jml_penawaran
    WHERE
nama_buah = NEW.nama_buah;
   ELSIF TG_OP = 'UPDATE' THEN
    UPDATE tb_stok_buah SET jml_pemesanan = jml_pemesanan - OLD.jml_penawaran + NEW.jml_penawaran
    WHERE
    nama_buah = NEW.nama_buah;
   ELSIF TG_OP = 'DELETE' THEN
    UPDATE tb_stok_buah SET jml_pemesanan = jml_pemesanan - OLD.jml_penawaran
    WHERE
    nama_buah = OLD.nama_buah;
   END IF;
   RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Lalu kita perlu utk membuat trigger menjalankan fungsi diatas. Sebelum itu agar tidak bingung, beberapa bagian dari fungsi diatas yaitu TG_OP singkatan dari Trigger Operation dan NEW/OLD merupakan tempat data dari kolom terkait, NEW utk data baru dan OLD utk data lama atau sebelumnya.

Kita buat trigger utk setiap data record tabel pemesanan berubah, berikut ini contohnya:

CREATE TRIGGER manage_pemesanan_buah
AFTER INSERT OR UPDATE OR DELETE
ON tb_penawaran_buah
FOR EACH ROW
EXECUTE PROCEDURE pemesanan_buah();

Sampai disini kita telah selesai, mari kita lakukan ujicoba. Pertama kita tambahkan dulu beberapa buah ke dalam tabel stok:

Contoh data sudah kita buat, selanjutnya kita buat contoh pemesanan sejumlah 100 buah NANGKA utk John:

Kita sudah buat pemesanan pertama, jika kita lakukan pengecekan kembali di tabel stok maka akan tampak perubahan di kolom jml_pemesanan:

SELECT * FROM tb_stok_buah;
  nama_buah  | stok_buah | jml_pemesanan
-------------+-----------+----------------
DURIAN |      500 |        0
NANGKA |      500 |      100

Jika kita melakukan koreksi terhadap jumlah pesanan John misalnya dari 100 buah menjadi 115 buah NANGKA maka jumlah pesanan di tabel stok juga tetap akan berubah:

UPDATE tb_penawaran_buah SET jml_penawaran = 115 WHERE id_penawaran = 1;

SELECT * FROM tb_stok_buah;
  nama_buah  | stok_buah | jml_pemesanan
-------------+-----------+--------------
DURIAN |      500 |        0
NANGKA |      500 |      115

Pada contoh tabel stok, kita menggunakan sebuah constraint yaitu CHECK. Ada beragam jenis constraint, bisa di lihat disini.

Kembali kita lanjutkan utk contoh query berikutnya, kita coba buat query dimana kita ingin menjual NANGKA yg telah dipesan.

UPDATE tb_stok_buah SET stok_buah = 100 WHERE nama_buah = 'NANGKA';
ERROR: new row for relation "tb_stok_buah" violates check constraint "tb_stok_buah_check"
DETAIL: Failing row contains (NANGKA, 100, 115).

Kemudian kita coba utk memesan lebih dari stok yg tersedia.

UPDATE tb_penawaran_buah SET jml_penawaran = 1100 WHERE id_penawaran = 1;
ERROR: new row for relation "tb_stok_buah" violates check constraint "tb_stok_buah_check"
DETAIL: Failing row contains (NANGKA, 500, 1100).
CONTEXT: SQL statement "UPDATE tb_stok_buah SET jml_pemesanan = jml_pemesanan - OLD.jml_penawaran + NEW.jml_penawaran
WHERE
nama_buah = NEW.nama_buah"
PL/pgSQL function pemesanan_buah() line 8 at SQL statement

Lalu utk kasus ini, jika kita ingin mengirimkan pesanan maka kita hanya perlu menghapusnya.

Akan tetapi, penggunaan DELETE disini hanyalah untuk contoh saja, jika kasusnya ada pada aplikasi nyata, maka langkah terbaik ialah menyimpan data pesanan menjadi arsip terlebih dahulu (misal disediakan tabel arsip) sebelum data pesanan dihapus agar aplikasi punya record pesanan yg telah dilakukan.

Saya kira hanya itu contoh Trigger yg bisa saya berikan, dengan contoh kasus yg sederhana dan mungkin akan lebih baik jika diterapkan pada aplikasi nyata. Silahkan isi kolom komentar jika ada yg ingin ditanyakan ataupun koreksi yg perlu dilakukan.

Belajar PostgreSQL: Membandingkan Data Dengan Menggunakan Operator - Part 2

Server programming bukan hanya berarti membuat fungsi didalam server database, sebenarnya ada banyak hal yang bisa dilakukan. Utk kasus yg kompleks, kita dapat membuat sebuah TYPE maupun OPERATOR sesuai yg kita butuhkan utk membandingkan sebuah jenis, misalnya kita ingin membandingkan antara buah nangka dan durian. Pertama kita definisikan TYPE yg akan dibuat, lalu berikan perintah utk membandingkan buah yang kita inginkan, katakanlah 1 buah nangka sebanding dengan 1.5 buah durian.

Kita buat TYPE terlebih dahulu, misalnya seperti dibawah ini:

CREATE TYPE KUANTITAS as (nama_buah text, kuantitas int);

Kemudian contoh penggunaannya yg sederhana sebagai berikut:

SELECT '("DURIAN", 3)'::KUANTITAS;
kuantitas_buah
------------
(DURIAN,3)
(1 row)

Kita lanjutkan dengan membuat sebuah fungsi utk proses perbandingan kuantitas antara 2 buah tersebut diatas:

CREATE OR REPLACE FUNCTION banding_kuantitas(
   jenis_buah_a KUANTITAS,
   jenis_buah_b KUANTITAS
) RETURNS BOOL AS
$$
BEGIN
   IF (jenis_buah_a.nama_buah = 'DURIAN' AND jenis_buah_b.nama_buah = 'NANGKA')
   THEN
     RETURN jenis_buah_a.kuantitas > (1.5 * jenis_buah_b.kuantitas);
   END IF;

   IF (jenis_buah_a.nama_buah = 'NANGKA' AND jenis_buah_b.nama_buah = 'DURIAN')
   THEN
     RETURN (1.5 * jenis_buah_a.kuantitas) > jenis_buah_b.kuantitas;
   END IF;

   RETURN jenis_buah_a.kuantitas > jenis_buah_b.kuantitas;
END;
$$
LANGUAGE plpgsql;

Masih dengan contoh yg sederhana, dimana ada kondisi didalamnya yg harusnya bisa dengan mudah dipahami maksud dari isi kondisi tersebut. Mari kita coba tes fungsinya, perlu diketahui hasil dari fungsi bukanlah sebuah STRING melainkan BOOLEAN sehingga output yg didapatkan adalah berupa hasil BOOLEAN, f utk false dan t utk true.

SELECT banding_kuantitas('("DURIAN", 3)'::KUANTITAS, '("NANGKA", 2)'::KUANTITAS);
banding_kuantitas
--------------
f
(1 row)

Kenapa kita dapat output false, lihat kondisi bagian pertama, karena memang kuantitas durian tidak lebih besar dari kondisi yg diharapkan. Kita coba lagi utk kondisi berikutnya.

SELECT banding_kuantitas('("DURIAN", 4)'::KUANTITAS, '("NANGKA", 2)'::KUANTITAS);
banding_kuantitas
--------------
t
(1 row)

Nah disini kita mendapatkan hasil true, karena memang kali ini kuantitas durian mencukupi sehingga perbandingan yg kita lakukan memenuhi kondisi yg sudah kita tentukan didalam fungsi.

Disini kita baru selesai pada bagian TYPE saja, mari kita lanjutkan dengan OPERATOR. Kita bisa membuat query diatas menjadi lebih bermakna ( katakanlah begitu :D ). Mari langsung kita buat sebuah OPERATOR utk menangani proses seperti query diatas.

CREATE OPERATOR > (
   leftarg = KUANTITAS,
   rightarg = KUANTITAS,
   procedure = banding_kuantitas,
   commutator = >
);

Mungkin dari contoh OPERATOR yg dibuat itu mungkin sedikit membingungkan, tapi sebenarnya itu sederhana. Kita hanya menentukan argument terkait ( utk detail bisa langsung ke official web PostgreSQL ya :D )

Mari kita coba langsung dengan masih menggunakan query perbadingan sebelumnya.

SELECT '("NANGKA", 2)'::KUANTITAS > '("DURIAN", 2)'::KUANTITAS;
?column?
----------
t
(1 row)

SELECT '("NANGKA", 2)'::KUANTITAS > '("DURIAN", 3)'::KUANTITAS;
?column?
----------
f
(1 row)

Hasil perbandingan yg didapatkan adalah sama. Cukup mudah dimengerti ( saya harap begitu :D ), dengan membuat hal semacam ini tentunya akan dapat membantu kita mendapatkan data ke dalam aplikasi menjadi lebih to the point tanpa harus membuat query utk kondisi yg diharapkan.

Jangan lupa silahkan isi kolom komentar jika ada yg ingin ditanyakan ataupun koreksi yg saya harus lakukan.

Belajar PostgreSQL: Dasar Pemrograman Server pada PostgreSQL - Part 1

PostgreSQL bukan hanya sekedar sebuah storage system yang mana cara untuk menggunakan hanyalah dengan mengeksekusi SQL statement. Sebenarnya itu hanyalah bagian kecil dari fiturnya.

Di seri kali ini kita akan membahas beberapa aspek pemrograman PostgreSQL server, bukan secara menyeluruh karena fitur yang tersedia sangat banyak dan saya hanya akan membuat beberapa penggunaan fitur yang umum ( bisa dikatakan begitu :D ). Saya akan membuat beberapa part utk seri ini, sama seperti seri belajar MySQL sebelumnya. Tentunya disini saya tidak menjelaskan penggunaan SQL secara umum tetapi lebih kepada fitur-fitur ataupun penggunaan PostgreSQL itu sendiri.

Developer dapat membuat sebuah program dengan beragam pilihan bahasa pemrograman yang tersedia. Ketika membuat sebuah aplikasi, biasanya logic pengumpulan ataupun kelola database aplikasi langsung pada sisi aplikasi, tentunya cara ini sudah umum digunakan dalam desain sebuah aplikasi. Akan tetapi sebenarnya kita bisa melakukan pemrograman didalam database server.

Mari kita lihat contoh sederhana. Berikut ini kita sediakan tabel beserta contoh datanya sebagai berikut:

CREATE TABLE tb_tabungan(pemilik text, saldo numeric);
INSERT INTO tb_tabungan VALUES ('John', 500000);
INSERT INTO tb_tabungan VALUES ('Jenny', 2000);

Umumnya, untuk berinteraksi dengan data tersebut diatas adalah dengan menggunakan SQL query. Misalkan kita ingin memindahkan dana senilai 20.000 dari akun John ke akun Jenny, dapat dilakukan dengan query berikut:

UPDATE tb_tabungan SET saldo = saldo - 20000 WHERE pemilik = 'John';
UPDATE tb_tabungan SET saldo = saldo + 20000 WHERE pemilik = 'Jenny';

Dari contoh diatas kita sudah bisa mendapatkan hasil yang diinginkan. Namun misalkan jika John tidak memiliki cukup saldo pada rekeningnya, maka seharusnya tidak ada transaksi yang akan terjadi. Utk mengatasi hal semacam ini kita dapat lakukan di sisi database server dengan membuat sebuah fungsi yg biasa dikenal dengan User-defined function (UDF). PostgreSQL sudah memiliki bahasa pemrograman sendiri yaitu PL/pgSQL. PL adalah singkatan dari Programming Language dan pgSQL adalah singkatan dari PostgreSQL, ya itulah PL/pgSQL.

Kita coba buat sebuah fungsi sederhana dengan menggunakan PL/pgSQL, bisa copas jika tidak mau repot :D

CREATE OR REPLACE FUNCTION transfer(
  i_pembayar text,
  i_penerima text,
  i_saldo numeric(15,2)
) RETURNS text AS
$$
DECLARE
  saldo_pembayar numeric;
BEGIN
  SELECT saldo INTO saldo_pembayar FROM tb_tabungan
  WHERE
  pemilik = i_pembayar FOR UPDATE;

  IF NOT FOUND THEN
    RETURN 'Nama pembayar tidak ditemukan';
  END IF;
  IF saldo_pembayar < i_saldo THEN
    RETURN 'Dana dalam saldo tidak mencukupi';
  END IF;

  UPDATE tb_tabungan SET saldo = saldo + i_saldo
  WHERE
  pemilik = i_penerima;

  IF NOT FOUND THEN
    RETURN 'Nama penerima tidak ditemukan';
  END IF;

  UPDATE tb_tabungan SET saldo = saldo - i_saldo
  WHERE
  pemilik = i_pembayar;

  RETURN 'Transfer berhasil';
END;
$$
LANGUAGE plpgsql;

Karena dasarnya bahasa inggris, tentunya akan mudah dipahami maksud dari fungsi diatas yang hanya berupa logic kondisi. Oh ya, perlu diperhatikan bahwa tanda ( ; ) disini masih sangat berpengaruh jadi jangan sampai lupa karena akan terjadi error.

Kita cek terlebih dahulu data yang kita punya:

SELECT * FROM tb_tabungan;
pemilik | saldo
---------+---------
John | 500000.00
Jenny | 20000.00
(2 rows)

Dalam fungsi transfer yang kita buat, terdapat 3 argument yaitu berupa input nama pembayar, input nama penerima, dan nominal saldo, lalu cara menggunakannya adalah sebagai berikut:

SELECT * FROM transfer('John', 'Jenny', 20000.00);
transfer
----------
Transfer berhasil
(1 row)

Lalu cek kembali datanya, tentunya akan berubah saldo milik si Jenny.

Kemudian kita coba utk jenis kondisi yang tersedia dengan memberikan nama penerima, nama pembayar yang tidak tersedia dan saldo yang tidak cukup, berikut ini contohnya:

- Nama pembayar yg tidak tersedia

SELECT * FROM transfer('Momo', 'Jenny', 20000.00);
transfer
----------
Nama pembayar tidak ditemukan
(1 row)

- Nama penerima yg tidak tersedia

SELECT * FROM transfer('John', 'Jack', 20000.00);
transfer
----------
Nama penerima tidak ditemukan
(1 row)

- Saldo pembayar yg tidak cukup

SELECT * FROM transfer('John', 'Jenny', 1500000.00);
transfer
----------
Dana dalam saldo tidak mencukupi
(1 row)

Sangat bagus bukan? tentunya dengan adanya fungsi ini kita hanya perlu memanggilnya dalam aplikasi yang kita buat daripada membuat logic yang sama didalam aplikasi yang berupa SQL statement terlebih jika digunakan pada beberapa modul akan lebih baik menggunakan cara seperti contoh diatas, ya meskipun tidak salah ataupun bukan keharusan hanya saja cara seperti ini sebenarnya bisa dilakukan dan mungkin bisa saya bilang lebih baik dilakukan agar kode didalam aplikasi yang kita buat setidaknya tidak begitu banyak hanya karena SQL statement yg kita buat :D

Itulah contoh sederhana dari seri pembuka pemrograman PostgreSQL, cukup banyak memang dan saya berharap dengan ini kita bisa lanjutkan ke part berikutnya. Jangan lupa silahkan berikan komentar jika ada yang ingin ditanyakan ataupun koreksi yang harus saya lakukan.

Cara Install PostgreSQL di Debian

PostgreSQL merupakan salah satu RDMS (Relational Database Management System) populer dari banyak jenis RDMS yg tersedia. Saya sendiri telah menggunakannya sejak lama dan saya sangat suka dengan beragam fitur yg tersedia. Mulai dari sini saya akan membuat seri artikel baru utk artikel belajar PostgreSQL, namun sebelum itu kita perlu utk menginstall ke dalam OS dan juga saya akan menyertakan sedikit dasar cara menggunakannya, saya akan menggunakan Debian Linux versi 9 dan tentunya cara installasi mungkin akan sama utk jenis OS turunan debian.

Langkah pertama silakan update package terlebih dahulu dan kemudian install 2 package yg dibutuhkan:

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

Setelah proses installasi selesai, utk mengakses ke shell PostgreSQL bisa dilakukan dengan 2 cara, langkah pertama sbb:

$ sudo -i -u postgres
$ psql
postgres=# \q <== \q utk keluar dari shell

Kemudian cara kedua adalah yg paling sering saya gunakan:

$ sudo -u postgres psql
postgres=# \q <== \q utk keluar dari shell

Sampai disini kita tahu cara mengakses shell milik PostgreSQL. Perlu utk diketahui, PostgreSQL tidak membedakan antara nama pengguna maupun grup dan lebih ke istilah spesifik yaitu "role" yg berguna utk mengatur autentikasi dan autorisasi. Dan juga PostgreSQL tidak memiliki user/role root, default user/role nya adalah postgres.

Jika kita ingin membuat akses shell baru utk PostgreSQL, kita perlu terlebih dahulu membuat sebuah role. Jika kita langsung mencoba mengakses shell misalnya:

$ sudo -u john psql
psql: FATAL: role "john" does not exist

Maka akan muncul error seperti contoh diatas, oleh karena itu kita perlu membuat sebuah role. Tapi sebelum itu kita harus masuk ke dalam shell dulu, ulangi cara diatas utk masuk ke shell, lalu ikuti perintah dibawah ini:

postgres=# \du <== \du utk melihat daftar roles
                                   List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# CREATE ROLE test_role1;
postgres=# \du
                                   List of roles
Role name |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_role1 | Cannot login                                        | {}

Role baru sudah berhasil dibuat tetapi tanpa ada hak akses utk login. Utk detail mengenai perintah CREATE ROLE bisa dilihat dengan perintah berikut:

postgres=# \h CREATE ROLE;

Akan tetapi ada cara yg lebih interaktif yaitu menggunakan shell user, berikut ini contohnya:

$ sudo -u postgres createuser --interactive
Enter name of role to add: test_role2
Shall the new role be a superuser? (y/n) y

Kemudian jika kita lihat kembali role yg ada akan seperti contoh berikut:

postgres=# \du
                                   List of roles
Role name |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_role1 | Cannot login                          | {}
test_role2 | Superuser, Create role, Create DB             | {}

Sampai disini kita sudah berhasil membuat role, masih ada langkah berikutnya sebelum kita bisa menggunakan role yg baru. Jika kita mencoba langsung mengakses shell dengan role baru maka akan terjadi error meskipun role sudah kita buat.

$ sudo -i -u test_role2 psql
sudo: unknown user: test_role2
sudo: unable to initialize policy plugin

Hal ini dikarenakan autentikasi yg digunakan PostgreSQL adalah berdasarkan linux user, maka dari itu kita perlu membuat sebuah linux user baru dengan nama role yg sama:

$ sudo adduser test_role2 <== (ikuti perintah output nya)

Jika kita sudah selesai menambahkan user baru, kita juga masih belum bisa mengakses shell, jika kita coba maka akan tetap terjadi error:

$ sudo -i -u test_role2 psql
psql: FATAL: database "test_role2" does not exist

Karena secara default role yg baru kita buat sebelumnya tidak memiliki database dan ketika masuk ke shell harusnya langsung masuk ke default database milik role, oleh karena itu kita perlu lakukan langkah terakhir yaitu membuat database default utk role:

$ sudo -u postgres createdb test_role2
$ sudo -i -u test_role2 psql

Dengan begini kita sudah bisa masuk ke dalam shell dengan role baru. Memang cukup sulit tapi jika sudah terbiasa maka akan mudah. Dan contoh diatas hanyalah sekedar dasar penggunaannya, masih banyak lagi cara ataupun opsi terkait proses pembuatan role yg bisa langsung dilihat pada web official PostgreSQL.

Sedikit tambahan utk penggunaan shell, berikut ini beberapa perintah dasar saat menggunakan shell:

Masuk ke database tertentu

\c database_name;

Keluar dari shell

Melihat semua database

Melihat semua schema

\dn

Melihat semua store procedure dan fungsi

\df

Melihat semua tabel

\dt

Menampilkan isi sebuah store procedure ataupun fungsi

\df+ function_name

Menampilkan hasil query dalam pretty-format

Menampilkan semua user

\du

Itulah dasar penggunaan dan cara install PostgreSQL server dalam OS linux. Jika ada yg ingin ditanyakan silahkan isi kolom komentar dibawah dan berikan koreksi juga jika ada yg salah.