Dec 6, 2024 by Robert Gravelle

Salah satu fitur PostgreSQL yang menonjol adalah dukungannya yang luas untuk fungsi dan tipe data yang ditentukan pengguna. Hal ini memungkinkan pengembang untuk membuat fungsi konversi, operator, dan agregat khusus. Agregat menawarkan cara yang hebat untuk melakukan kalkulasi dan transformasi yang rumit pada data, melampaui fungsi agregat SQL standar seperti SUM, AVG, dan COUNT. Baik Navicat for PostgreSQL dan Navicat Premium memudahkan penulisan fungsi dan agregat khusus yang terintegrasi dengan mulus dengan database, berkat antarmuka pengguna grafis (GUI) khusus mereka. Yang perlu kita lakukan hanyalah memberikan beberapa detail dan Navicat akan menghasilkan pernyataan pgSQL untuk kita! Dalam blog hari ini, kita akan membuat agregat untuk bekerja dengan DVD Rental database yang menggabungkan judul film menurut kategori.

Tentang Agregat

Agregat adalah fitur dasar SQL yang memungkinkan Anda melakukan perhitungan atau transformasi pada sekumpulan baris dan mengembalikan satu hasil. Fungsi agregat yang paling umum adalah SUM, AVG, COUNT, MIN, dan MAX, yang memungkinkan Anda meringkas data dengan cepat dengan menghitung total, rata-rata, hitungan, nilai minimum, dan nilai maksimum.

Namun, fungsi agregat bawaan yang disediakan oleh SQL tidak selalu memenuhi kebutuhan spesifik aplikasi. Di sinilah kemampuan untuk membuat agregat kustom menjadi berguna. Agregat kustom memungkinkan Anda untuk menentukan logika Anda sendiri untuk meringkas dan mengubah data, melampaui kumpulan agregat SQL standar. Proses ini biasanya melibatkan pendefinisian fungsi transisi status, yang dipanggil untuk setiap baris guna memperbarui akumulator, serta fungsi final opsional yang dipanggil untuk menghasilkan hasil agregat akhir.

Menghasilkan Fungsi Transisi dan Final

Fungsi transisi kita, array_append_state(), akan dipanggil untuk setiap baris guna memperbarui status agregat.

Untuk mengakses editor fungsi Navicat, klik tombol Fungsi di bilah tombol utama, lalu klik "Fungsi Baru" di bilah alat Objek:

Navicat akan memulai dengan definisi fungsi utama. Dari sana, kita akan menyediakan nama fungsi, parameter input, dan isi:

CREATE FUNCTION "public"."array_append_state" (current_state text[], new_value text)
  RETURNS text[] AS $BODY$
BEGIN
  RETURN array_append(current_state, new_value);
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Setelah selesai, kita dapat mengeklik Simpan untuk membuat fungsi.

Sekarang kita akan kembali ke tab Objek dan mengeklik "Fungsi Baru" untuk membuat fungsi akhir.

Fungsi array_to_comma_string() akan mengambil array judul film dan menyisipkan koma di antara setiap elemen:

CREATE FUNCTION "public"."array_to_comma_string" (state text[])
  RETURNS text AS $BODY$
BEGIN
  RETURN array_append(state, ', ');
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Membuat Fungsi Agregat comma_concat()

Sekarang kita dapat menyambungkan dua fungsi kita ke Editor Agregat Navicat. Kita dapat mengakses editor dengan mengeklik tombol Lainnya di bilah tombol utama, lalu memilih "Agregat" dari menu konteks:

Pada form ini, kita akan menetapkan tipe Input “text”, memasukkan tipe State dari “text()”dan memasok fungsi State dan Final kita. Serta, memastikan bahwa kondisi awal adalah array kosong (“{}”):

Kita dapat melihat SQL yang dihasilkan dengan mengklik tab Preview:

CREATE AGGREGATE "public"."Untitled" (In "pg_catalog"."text")
(
  SFUNC = "public"."array_append_state",
  STYPE = "pg_catalog"."text[]",
  FINALFUNC = "public"."array_to_comma_string",
  INITCOND = "{}",
  PARALLEL = UNSAFE
);

ALTER AGGREGATE "public"."Untitled"("pg_catalog"."text") OWNER TO "postgres";

Perhatikan bahwa nama agregat adalah "Tanpa Judul". Navicat akan meminta kita memasukkan nama saat kita menekan tombol Simpan dan menjalankan perintah dengan nama yang kita berikan.

Menggunakan Agregat Kustom Kita

Sekarang kita dapat memanggil fungsi agregat seperti fungsi lainnya. Berikut ini adalah kueri yang mengambil daftar film berdasarkan kategori:

SELECT 
    c.name AS category,
    comma_concat(f.title) AS movies
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
GROUP BY c.name
ORDER BY c.name;

Kesimpulan

Dalam blog hari ini, kami membuat agregat PostgreSQL khusus di Navicat Premium untuk bekerja dengan basis data Persewaan DVD yang menggabungkan judul film berdasarkan kategori.

Tertarik mencoba Navicat Premium 17? Anda dapat mengunduhnya untuk uji coba GRATIS selama 14 hari yang berfungsi penuh. Tersedia untuk sistem operasi Windows, macOS, dan Linux.