PL/PGSQL dan Trigger dalam PostgreSQL

PL/PGSQL dan Trigger dalam PostgreSQL - Fungsi SQL (SQL Function) merupakan kumpulan query detail dan panjang yang dibungkus menjadi satu dan disimpan dalam database. Apabila diperlukan kita hanya tinggal mengaksesnya tanpa mengetikan query detail. Faktor yang perlu diperhatikan dalam membuat fungsi antara lain:

• Nama Fungsi
• Nomor dari fungsi argument
• Tipe data dari setiap argument
• Tipe dari hasil fungsi
• Fungsi action
• Bahasa yang digunakan oleh fungsi action

PL/PGSQL merupakan bahasa yang lain untuk membuat sebuah fungsi, biasanya bahasa ini digunakan untuk menangani fungsi yang lebih kompleks. Pl/pgsql sudah terdapat dalam instalasi PostgreSQL. Beberapa keuntungan penggunaan Fungsi PL/PGSQL :

1. Meningkatkan kinerja karena mengurangi pengiriman kode dari klien ke server.
2. Meningkatkan keamanan karena pengaksesan data tertentu ditangani dalam server.
3. Meningkatkan konsistensi data saat sejumlah aplikasi memanggil prosedur yang sama.

Sebaliknya kelemahannya yaitu server akan lebih terbebani karena banyak proses yang harus ditangani. Sedangkan query PL/PGSQL agar lebih mudah di pahami akan dibagi menjadi 2 yaitu pembuatan fungsi dan pembuatan definisi.

Kali ini akan membahas mengenai fungsi dasar penggunaan PL/PGSQL dan TRIGER pada PostgreSQL. Melalui tutorial ini, diharapkan kita dapat menggunakan berbagai model fungsi PL/PGSQL dan TRIGER pada pengolahan database PostgreSQL. Sebagai studi kasus, Kelas Informatika telah mempunyai database mahasiswa, nantinya akan kita gunakan sebagai bahan praktek penggunaan ragam fungsi PL/PGSQL dan TRIGER pada PostgreSQL.

Membuat Fungsi Konversi

Pertama akan dicontohkan cara membuat fungsi konversi suhu dari fahrenheit ke derajat celcius dengan menggunakan rumus konversi berikut:

celcius = 5/9 * (nilai Fahrenheit-32)

Berdasarkan rumus tersebut dapat kita konversi kedalam query berikut:

postgres=> create function konversi (float)postgres-> returns floatpostgres-> as 'select (($1-32) * 5/9);'postgres-> language 'sql';

Setelah query dimasukkan selanjutnya lakukan pengecekan apakah fungsi yang telah dibuat sudah sesuai dengan rumus. Pengecekan dilakukan dengan menggunakan select (spasi) nama fungsi (spasi) nilai parameter yang akan dimasukkan.

Keterangan: Terlihat bahwa pengecekan dilakukan sebanyak tiga kali, yakni konversi 0, 50 dan 100.

Membuat Fungsi Untuk Mencari Alamat

Selanjutnya akan kita coba membuat sebuah fungsi yang digunakan untuk mencari alamat pada tabel mahasiswa. Dimana ketika kita memasukkan nama yang terdapat pada tabel mahasiswa nantinya akan memunculkan alamat berdasarkan nama mahasiswa yang kita masukkan tadi. Untuk membuat fungsi tersebut kita dapat menggunakan query berikut:

postgres=> create function cari_alamat (varchar)postgres-> returns varcharpostgres-> as 'declare hasil varchar;postgres'> beginpostgres'> select into hasil mah_alamat as alamatpostgres'> from mahasiswapostgres'> where mah_nama = $1;postgres'> return hasil;postgres'> end;'postgres-> language 'plpgsql';

Pastikan keberhasilan membuat fungsi pencarian ini dengan melakukan pengujian. Kita coba mencari alamat seseorang berdasarkan nama yang telah ada pada tabel mahasiswa.

Keterangan: Terlihat terdapat pesan create function yang berarti fungsi telah berhasil dibuat. Kemudian pada gambar tersebut pula ada contoh pengecekan fungsi dengan memasukkan data dengan nama ahmad yang kemudian memunculkan alamat yang bersangkutan.

Memberikan Predikat Nilai 

Berikutnya akan dipraktekkan cara membuat sebuah fungsi untuk memberikan predikat nilai berdasarkan ketentuan berikut:

• Apabila nilai melebih 100 ataupun kurang dari 0, maka akan memunculkan pesan “nilai salah”.
• Apabila nilai lebih dari 90, maka pesan yang akan muncul adalah “nilai A”.
• Apabila nilai lebih dari atau sama dengan 70 dan kurang dari 90, maka pesan yang akan muncul adalah “nilai B”.
• Apabila nilai lebih dari atau sama dengan 60 dan kurang dari 70, maka pesan yang akan muncul adalah “nilai C”.
• Apabila nilai lebih dari atau sama dengan 50 dan kurang dari 60, maka pesan yang akan muncul adalah “nilai D”.
• Sedangkan apabila nilai lebih dari atau sama dengan 0 dan kurang dari 50, maka pesan yang akan muncul adalah “nilai E”.

Nah, berdasarkan ketentuan tersebut kita dapat menggunakan fungsi percabangan (if - else). Berikut query selengkapnya yang telah berhasil dibuat:

postgres=> create function nilai (x integer)postgres-> returns varcharpostgres-> as 'declare predikat varchar;postgres'> beginpostgres'> if x>100 or x<0 thenpostgres'> predikat = ''nilai salah'';postgres'> elsif x>90 thenpostgres'> predikat = ''nilai A'';postgres'> elsif x>=70 and x<90 thenpostgres'> predikat = ''nilai B'';postgres'> elsif x>=60 and x<70 thenpostgres'> predikat = ''nilai C'';postgres'> elsif x>=50 and x<60 thenpostgres'> predikat = ''nilai D'';postgres'> elsif x>=0 and x<50 thenpostgres'> predikat = ''nilai E'';postgres'> end if;postgres'> return predikat;postgres'> end;'postgres-> language 'plpgsql';

Jangan lupa untuk melakukan pengujian terhadap fungsi yang telah dibuat. Caranya tidak jauh berbeda dengan sebelumnya.

Keterangan: Kita lihat pada gambar, disana telah dilakukan pengujian sebanyak tiga kali dengan besaran nilai yang berbeda pada masing-masing proses pengecekan.

Mengelompokkan Jenis Bilangan

Selanjutnya akan dipraktekkan cara membuat fungsi untuk mencari bilangan ganjil atau genap. Seperti kasus sebelumnya, kita gunakan fungsi percabangan (if - else). Sedangkan untuk proses pengelompokan menggunakan fungsi modulus. Berikut query selengkapnya yang telah berhasil dibuat:

postgres=> create function jenis_bilangan (x integer)postgres-> returns varcharpostgres-> as 'declare hasil varchar;postgres'> beginpostgres'> if x%2 = 0 thenpostgres'> hasil = ''bilangan genap'';postgres'> elsepostgres'> hasil = ''bilangan ganjil'';postgres'> end if;postgres'> return hasil;postgres'> end;'postgres-> language 'plpgsql';

Tetaplah untuk melakukan pengujian terhadap query yang telah dibuat, hal tersebut merupakan salah satu indikator keberhasilan, apakah fungsi tersebut telah sesuai atau tidak. 

Fungsi Trigger PostgreSQL

Terakhir akan dijelaskan tentang cara membuat fungsi untuk menambahkan kolom modifikasi pada tabel mahasiswa. Dimana setiap ada aktivitas penginputan (insert) maupun pembaharuan (update) data, maka tanggal pada kolom modifikasi akan menunjukkan tanggal perubahan tersebut. Untuk menyelesaikan hal tersebut, kita perlu melewati beberapa tahapan. 

Penambahakan Kolom 

Pertama kita harus menambahkan kolom terhadap tabel mahasiswa, kolom tersebut nantinya akan digunakan sebagai tempat untuk  meletakkan waktu perubahan data. Kita tambahkan kolom tanggal_pembaruan untuk menempatkan data perubahan tersebut. Sedangkan untuk query kita gunakan fungsi alter table.

Keterangan: Terlihat pada struktur tabel diatas, proses penambahan kolom tanggal_pembaruan telah berhasil ditambahkan. Nantinya kolom tersebut akan dibuat untuk meletakkan waktu perubahan data.

Pembuatan Fungsi

Setelah proses penambahan kolom telah berhasil, selanjutnya kita buat fungsi tersebut. Dalam hal ini, kita dapat menggunakan query berikut:

postgres=> create function modifikasi()postgres-> returns opaquepostgres-> as 'beginpostgres'> new.tanggal_pembaruan := current_date;postgres'> return new;postgres'> end;'postgres-> language 'plpgsql';

Nah, setelah query dimasukkan dan memunculkan pesan “CREATE FUNCTION”, artinya kita telah berhasil membuat fungsi yang dimaksudkan. Selanjutnya kita perlu membuat sebuah trigger yang berfungsi untuk memanggil fungsi secara otomatis ketika kita melakukan penginputan (insert) ataupun pembaharuan (update) data pada tabel mahasiswa. berikut query dapat digunakan:

postgres=> create trigger implementasipostgres-> before insert or updatepostgres-> on mahasiswapostgres-> for each rowpostgres-> execute procedure modifikasi();

Pengujian

Setelah kedua proses tersebut telah dilewati, barulah kita dapat melakukan pengujian terhadap tabel mahsiswa. Pengujian kita lakukan dengan cara input dan update data mahasiswa. Berikut dokumentasi hasil pengujian yang telah dilakukan.

Perhatikan pada gambar diatas, terlihat disana telah dilakukan input data dengan NIM 16, dilanjutkan dengan pembaharuan (update) alamat pada data dengan NIM 15. Proses keduanya telah memunculkan tanggal perubahan pada kolom tanggal_pembaruan. Hal tersebut menandakan bahwa fungsi trigger yang telah kita buat telah sesuai.

Demikian tulisan kali ini yang membahas tentang PL/PGSQL dan Trigger dalam PostgreSQL. Segala pertanyaan, kritik maupun saran dapat anda sampaikan melalui kolom komentar dibawah. Jangan lupa untuk selalu mengupdate informasi seputar teknologi informasi dan multimedia melalui Blog Kelas Informatika, atau dengan cara mengikuti fanspage Kelas Informatika. Salam 😊

Berbagi :