Membuat dan menggunakan fungsi di Azure Database for PostgreSQL

  • 5 menit

Kami sudah mempelajari bahwa PostgreSQL mendukung berbagai bahasa. Fungsi dapat dikategorikan ke dalam empat jenis berbeda:

  • Fungsi yang ditulis dalam SQL.
  • Fungsi bahasa prosedural, ditulis dalam bahasa prosedural yang didukung seperti PL.pgSQL.
  • Fungsi internal.
  • Fungsi bahasa C.

Selain itu, tujuan fungsi juga dapat dikategorikan sebagai volatil, tidak dapat diubah, atau stabil.

Fungsi volatil (default) dapat memodifikasi database dan mungkin tidak selalu mengembalikan hasil yang sama dengan parameter input yang sama setiap kali. Jadi, setiap kali fungsi ini dipanggil, fungsi ini harus dievaluasi ulang.

Fungsi stabil tidak dapat mengubah database dan mengembalikan hasil yang sama jika melewati argumen yang sama dan berjalan dalam pernyataan yang sama. Jika fungsi ini dipanggil beberapa kali, pengoptimal kueri dapat menggunakan hasil dari terakhir kali dipanggil.

Fungsi yang tidak dapat diubah tidak dapat mengubah database dan mengembalikan hasil yang sama jika melewati argumen yang sama, terlepas dari kueri yang memanggilnya.

Volatilitas fungsi membuat perbedaan besar terhadap efisiensi dengan mana pengoptimal kueri menanganinya.

Membuat fungsi

Fungsi mengembalikan satu nilai dan dapat digunakan dalam pernyataan SELECT.

Sintaks untuk membuat fungsi adalah:

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

Seperti prosedur tersimpan, simbol $$ digunakan untuk memulai dan mengakhiri string.

Fungsi mengambil parameter berikut:

  • nama - secara opsional sertakan nama skema.
  • argmode - mode dari argumen. Dapat berupa IN, OUT, INOUT, atau VARIADIC. Defaultnya adalah IN. VARDIAC adalah jumlah argumen input yang tidak terdefinisi dengan jenis yang sama, dan diikuti oleh argumen OUT. Argumen OUT dan INOUT tidak dapat digunakan bersama dengan notasi RETURNS TABLE.
  • nama argumen - nama argumen.
  • argtype - jenis data argumen. Dapat berupa jenis dasar, komposit, atau domain, atau mereferensikan jenis kolom tabel. Jenis kolom ditulis sebagai table_name.column_name%TYPE. Jenis data ini dapat membantu membuat fungsi tidak bergantung pada perubahan definisi tabel.
  • t_expr - Nilai default (dengan jenis yang sama) jika parameter tidak ditentukan. Hanya parameter IN dan INOUT yang memiliki nilai default. Parameter input yang mengikuti parameter dengan nilai default juga harus memiliki nilai default.
  • jenis pengembalian - Jenis data yang dikembalikan, yang dapat berupa tipe dasar, komposit, atau domain, atau merujuk pada tipe kolom tabel. Jika fungsi tidak mengembalikan nilai, tentukan jenis pengembalian sebagai batal. Ketika ada parameter OUT atau INOUT, klausul RETURNS dapat dihilangkan. Jika ada, harus selaras dengan jenis hasil yang ditentukan oleh parameter output: RECORD jika ada beberapa parameter output, atau memiliki jenis yang sama dengan satu parameter output. Pengubah SETOF menunjukkan bahwa fungsi mengembalikan sekumpulan item, bukan satu item. Jenis kolom dirujuk dengan menulis table_name.
  • column_name - Nama kolom output dalam sintaks RETURNS TABLE. Parameter ini mendeklarasikan parameter OUT bernama, kecuali bahwa RETURNS TABLE juga menyiratkan RETURNS SETOF.
  • column_type - Jenis data kolom output dalam sintaks RETURNS TABLE.
  • lang_name - bahasa yang digunakan untuk menulis prosedur. Defaultnya adalah sql jika sql_body ditentukan. Dapat berupa sql, c, internal, atau nama bahasa prosedural yang ditentukan pengguna, misalnya, plpgsql.

Gunakan kata kunci IMMUTABLE, STABLE, atau VOLATILE sebagai petunjuk untuk pengoptimal kueri tentang fungsi tersebut. VOLATILE adalah nilai bawaan.

Memanggil suatu fungsi

Fungsi dapat digunakan dalam kueri dengan meneruskan parameter yang relevan ke dalamnya. Misalnya:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Fungsi bawaan

PostgreSQL menyertakan banyak fungsi bawaan yang dapat Anda gunakan dalam kueri Anda. Ini mencakup membuat perbandingan, menggabungkan data, fungsi matematika, dll. Lihat dokumentasi online untuk daftar lengkap fungsi PostgreSQL.

Contoh fungsi string bawaan adalah substring.

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

Fungsi ini mengambil tiga parameter input:

  • String (jenis data teks)
  • DARI awal (ketik tipe bilangan bulat)
  • Jumlah FOR (ketik bilangan bulat)

Substring mengembalikan bagian dari teks input, dimulai dari karakter awal , dan berhenti setelah karakter hitungan . Misalnya:

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

Fungsi ini sama dengan substr:

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph

Nota

Jika Anda terbiasa dengan fungsi, Anda akan melihat bahwa versi pertama menggunakan kata kunci daripada koma untuk memisahkan argumen. PostgreSQL menyediakan kedua versi fungsi ini.