Menu

CREATE FUNCTION dan Stored Procedure di PostgreSQL

CREATE FUNCTION mendefinisikan fungsi yang kamu panggil di dalam query, ditulis dalam SQL atau PL/pgSQL. CREATE PROCEDURE mendefinisikan rutin yang kamu jalankan dengan CALL, yang bisa melakukan commit transaksi di tengah jalan.

Halaman ini dilengkapi editor yang bisa dijalankan. Edit, jalankan, dan lihat output-nya seketika.

CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; mendefinisikan fungsi yang bisa dipanggil query seperti fungsi bawaan. Isinya ditulis di antara tanda $$, dalam SQL biasa atau dalam PL/pgSQL, bahasa prosedural PostgreSQL. CREATE PROCEDURE (PostgreSQL 11+) mendefinisikan rutin yang dijalankan dengan CALL.

with_tax(50) memakai tarif default dan mengembalikan 60; with_tax(50, 0.1) mengembalikan 55. Argumen juga bisa diberikan berdasarkan nama: with_tax(50, rate => 0.1).

Pakai LANGUAGE plpgsql saat fungsinya butuh variabel, IF, perulangan, atau penanganan error. Variabel ditulis di bagian DECLARE, dan kodenya di antara BEGIN dan END:

Paket 4 kg dikenai biaya dasar 5; paket ekspres 20 kg dikenai (5 + 10 * 0.5) * 2 = 20. Di dalam PL/pgSQL, RAISE NOTICE 'value: %', cost; mencetak pesan dan RAISE EXCEPTION menghentikan eksekusi dengan error.

Saat logikanya bisa dinyatakan dengan SQL biasa, utamakan LANGUAGE sql: planner bisa meng-inline fungsi SQL sederhana ke dalam query pemanggilnya.

Mengembalikan sekumpulan baris

RETURNS TABLE (...) membuat fungsi yang mengembalikan baris. Panggil di FROM seperti tabel:

orders_of('ana') mengembalikan pesanan 1 dan 3. RETURNS SETOF orders mengembalikan baris utuh dari tabel orders alih-alih kolom yang didaftarkan.

Sejak PostgreSQL 14, fungsi SQL bisa punya isi SQL standar alih-alih string. PostgreSQL menyimpannya dalam bentuk yang sudah di-parse dan mencatat tabel dan kolom yang dipakainya, jadi semuanya tidak bisa dihapus selama fungsi itu ada:

CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
  SELECT count(*) FROM orders WHERE customer = who;
END;

Membuat stored procedure dan menjalankannya dengan CALL

Procedure tidak punya tipe kembalian; kamu menjalankannya dengan CALL. Procedure bisa mengubah data, dan saat CALL tidak berada di dalam blok transaksi eksplisit, ia bisa melakukan COMMIT di tengah jalan. Begitulah cara memproses pekerjaan besar secara bertahap (batch).

Akun 1 berakhir dengan 70 dan akun 2 dengan 80. Transfer sebesar 200 akan melanggar constraint CHECK pada akun 1, dan error-nya membatalkan kedua update.

FunctionProcedure
Dijalankan denganSELECT f(...), di bagian mana pun dalam queryHanya CALL p(...)
Mengembalikansebuah nilai, satu baris, atau sekumpulan baristidak ada (atau parameter OUT/INOUT)
COMMIT / ROLLBACK di dalamnyatidakya, di PL/pgSQL, saat tidak dipanggil di dalam blok transaksi
Sejakselalu adaPostgreSQL 11

Menandai volatility: IMMUTABLE, STABLE, VOLATILE

Label volatility memberi tahu planner apa yang boleh diasumsikan tentang sebuah fungsi. Default-nya VOLATILE.

  • IMMUTABLE: hasilnya hanya bergantung pada argumen dan tidak pernah berubah (with_tax). Wajib untuk fungsi yang dipakai di expression index.
  • STABLE: hasilnya bisa berubah antarpernyataan tapi tidak di dalam satu pernyataan, misalnya fungsi yang membaca tabel (orders_of).
  • VOLATILE: selain itu, seperti fungsi yang memakai random() atau mengubah data. PostgreSQL memanggilnya lagi untuk setiap baris.

Label yang salah menghasilkan hasil yang salah: fungsi IMMUTABLE yang membaca tabel bisa mengembalikan nilai usang dari index atau dari rencana yang di-cache.

Mengganti, overload, dan menghapus fungsi

CREATE OR REPLACE FUNCTION mengubah isi fungsi yang sudah ada tapi tidak bisa mengubah tipe kembaliannya; untuk itu, hapus dulu fungsinya. PostgreSQL mengenali fungsi dari nama dan tipe argumennya, jadi f(int) dan f(text) adalah dua fungsi berbeda. Untuk menghapus salah satu dari dua fungsi bernama sama, sebutkan tipe argumennya:

CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;

Bab PL/pgSQL mendokumentasikan perulangan, cursor, exception, dan SQL dinamis dengan EXECUTE. Untuk menjalankan fungsi secara otomatis saat baris berubah, pasang fungsi itu ke sebuah trigger.

Pertanyaan yang sering diajukan

Apa perbedaan function dan procedure di PostgreSQL?

Function mengembalikan nilai dan dipanggil di dalam query (SELECT f(x)). Procedure, yang ditambahkan di PostgreSQL 11, dijalankan sendiri dengan CALL dan bisa melakukan COMMIT atau ROLLBACK di dalam isinya; function selalu berjalan di dalam transaksi pemanggilnya.

Apa arti $$ di function PostgreSQL?

$$ adalah dollar quoting: menandai awal dan akhir sebuah literal string, jadi isi function bisa berisi tanda kutip tunggal tanpa perlu di-escape. Kamu juga bisa menambahkan tag, seperti $body$ ... $body$.

Bagaimana cara melihat kode function PostgreSQL?

Di psql, \df menampilkan daftar function dan \sf name mencetak definisi sebuah function. Di SQL, SELECT pg_get_functiondef('name'::regproc); mengembalikan pernyataan CREATE OR REPLACE FUNCTION yang lengkap.

Bisakah function PostgreSQL mengembalikan banyak baris?

Bisa. Deklarasikan sebagai RETURNS TABLE (col type, ...) atau RETURNS SETOF type dan panggil di klausa FROM seperti tabel: SELECT * FROM f(1);.

Ilustrasi bahasa pemrograman di Coddy

Belajar coding dengan Coddy

Mulai