Mencipta Fungsi Tersuai Dalam PostgreSQL

Dalam PostgreSQL, fungsi tersuai boleh dibuat untuk menyelesaikan masalah yang rumit.

Ini boleh ditulis menggunakan bahasa skrip PL/pgSQL lalai, atau ia boleh ditulis dalam bahasa skrip lain.

Python, Perl, Tcl dan R ialah beberapa bahasa skrip yang disokong.

Walaupun PL/pgSQL disertakan dengan sebarang pemasangan Postgres, untuk menggunakan bahasa lain memerlukan beberapa persediaan.

Memasang sambungan

Sebelum sambungan boleh digunakan, pakej sambungan perlu dipasang.

Di Ubuntu anda akan menjalankan:

Perl

sudo apt-get -y install postgresql-plperl-14

Nama pakej 'postgresql-plperl-14' adalah khusus untuk PostgreSQL versi 14. Jika anda menggunakan versi PostgreSQL yang berbeza, anda perlu menukar nombor versi dalam nama pakej agar sepadan dengan versi PostgreSQL anda yang dipasang.

Python 3

sudo apt-get install postgresql-plpython3-14

Mengaktifkan sambungan

Untuk mengaktifkan sambungan dalam PostgreSQL sambungan mesti ditakrifkan menggunakan pernyataan CREATE EXTENSION.

Perl

CREATE EXTENSION plperl;

Python

CREATE EXTENSION plpython3;

Hello contoh dunia

Setelah sambungan dibuat, fungsi tersuai boleh dibuat menggunakan sambungan itu.

Perl

CREATE OR REPLACE FUNCTION hello(name text) 
RETURNS text AS $$
    my ($name) = @_;
    return "Hello, $name!";
$$ LANGUAGE plperl;

Python

CREATE OR REPLACE FUNCTION hello(name text)
RETURNS text AS $$
    return "Hello, " + name + "!"
$$ LANGUAGE plpython3;

Memecahkan baris demi baris ini

CREATE OR REPLACE FUNCTION hello(name text)

Baris ini ialah cara fungsi dicipta dalam Postgres. Dengan menggunakan CREATE OR REPLACE, ia akan menimpa sebarang fungsi yang telah ditakrifkan dengan nama hello dengan fungsi baharu.

Menggunakan CREATE FUNCTION hello(teks nama) akan menghalang fungsi daripada menimpa fungsi sedia ada dan akan ralat jika fungsi itu sudah wujud.

---

RETURNS text AS $$

Ini mentakrifkan jenis data Postgres yang akan dikembalikan, adalah penting bahawa jenis data yang dinyatakan ialah jenis yang diiktiraf oleh Postgres. Jenis data tersuai boleh ditentukan, jika jenis tersuai sudah ditentukan.

$$ ialah pembatas untuk menandakan permulaan dan penghujung blok kod. Dalam baris ini ia menandakan permulaan blok kod.

Semua kod antara permulaan dan akhir $$ akan dilaksanakan oleh Postgres

---

$$ LANGUAGE plperl;

$$ menandakan penghujung skrip dan memberitahu Postgres bahasa yang mana skrip itu harus dihuraikan.

Menggunakan fungsi

Fungsi boleh digunakan seperti mana-mana fungsi Postgres terbina dalam

SELECT hello('world');

Ini akan mengembalikan lajur dengan nilai Hello world!

Fungsi boleh menjadi sebahagian daripada pertanyaan yang lebih kompleks:

SELECT id, title, hello('world') greeting FROM table;

Contoh yang lebih kompleks

Berikut ialah contoh fungsi yang menerima teks daripada medan dan mengembalikan kiraan perkataan.

CREATE OR REPLACE FUNCTION word_count(paragraph text)
RETURNS json AS $$
use strict;
use warnings;

my ($text) = @_;

my @words = $text =~ /\w+/g;
my $word_count = scalar @words;

my $result = '{' .
    '"word_count":' . $word_count .
'}';
return $result;
$$ LANGUAGE plperl;

Ini mengembalikan hasil berformat JSON dengan jumlah perkataan.

---

Kami boleh menambah statistik yang lebih terperinci pada fungsi.

CREATE OR REPLACE FUNCTION word_count(paragraph text)
RETURNS json AS $$
use strict;
use warnings;

my ($text) = @_;

my @words = $text =~ /\w+/g;

my $word_count = scalar @words;

my $sentence_count = ( $text =~ tr/!?./!?./ ) || 0;

my $average_words_per_sentence =
  $sentence_count > 0 ? $word_count / $sentence_count : 0;

my $result = '{' .
    '"word_count":' . $word_count . ',' .
    '"sentence_count":' . $sentence_count . ',' .
    '"average_words_per_sentence":"' . sprintf("%.2f", $average_words_per_sentence) . '"' .
'}';

return $result;
$$ LANGUAGE plperl SECURITY DEFINER;

Kini apabila kita menggunakannya dalam pertanyaan

SELECT word_count(text_field) word_count FROM table

Ia akan mengembalikan JSON seperti

{"word_count":116,"sentence_count":15,"average_words_per_sentence":"7.73"}

Pertimbangan keselamatan

Apabila menggunakan fungsi tersuai atau bahasa skrip luaran, terdapat pertimbangan keselamatan tambahan yang perlu diambil kira. Ia boleh menjadi tindakan menyulap untuk mendapatkan keseimbangan yang betul antara kebolehgunaan dan keselamatan.

Security Definer vs Security Invoker

Dalam fungsi sebelumnya, pilihan SECURITY DEFINER telah ditambahkan pada kenyataan fungsi cipta.

Adalah penting untuk memikirkan bagaimana anda mahu fungsi dijalankan dari sudut keselamatan.

Tingkah laku lalai ialah menggunakan SECURITY INVOKER. Ini akan menjalankan fungsi dengan keistimewaan pengguna yang menjalankan fungsi tersebut.

SECURITY DEFINER memberikan lebih kawalan ke atas keistimewaan yang diberikan kepada fungsi tersebut. Menggunakan mod ini, fungsi akan berjalan dengan keistimewaan pengguna yang mencipta fungsi tersebut.

Ini boleh menjadi baik dan buruk, jika fungsi dicipta oleh pengguna dengan keistimewaan terhad, maka terdapat sedikit kemudaratan yang boleh dilakukan kepada pangkalan data.

Jika fungsi dicipta oleh pengguna yang mempunyai keistimewaan capaian tinggi, maka fungsi tersebut akan dijalankan dengan keistimewaan yang sama. Bergantung pada jenis fungsi, ini boleh membenarkan pengguna menjalankan fungsi dengan lebih banyak keistimewaan terbuka daripada yang telah diberikan.

Ada masa di mana ini berguna, sebagai contoh, jika pengguna tidak mempunyai keistimewaan membaca pada jadual, tetapi dalam fungsi , membaca diperlukan, menggunakan SECURITY DEFINER boleh membenarkan keistimewaan baca yang diperlukan untuk fungsi itu dijalankan.

---

Sambungan yang dipercayai dan tidak dipercayai

Apabila membuat sambungan di atas, plperl dan plpython3 telah digunakan. Dalam kebanyakan keadaan, ini adalah sambungan yang sesuai untuk digunakan.

Pelanjutan ini mempunyai akses terhad kepada sistem fail pelayan dan panggilan sistem.

Pelanjutan juga boleh dibuat dengan u (plpython3u, plperlu)

Ini adalah sambungan yang tidak dipercayai dan membenarkan lebih banyak akses kepada sistem fail pelayan.

Mungkin terdapat kes di mana ini diperlukan, contohnya, jika anda ingin menggunakan modul Perl, Perpustakaan Python atau menggunakan panggilan sistem.

Dalam contoh di atas, output JSON dijana sebagai rentetan, jika dikehendaki, modul JSON perl boleh digunakan untuk mengekod data sebagai JSON. Untuk melakukan ini memerlukan penggunaan sambungan yang tidak dipercayai untuk mengakses modul JSON.

Adalah dinasihatkan untuk tidak menggunakan sambungan yang tidak dipercayai, tetapi jika perlu, gunakan dengan berhati-hati dan fahami potensi risiko.

Jika Perl sedang digunakan, Perl akan berjalan dalam mod taint apabila sambungan yang tidak dipercayai sedang digunakan.

Fikiran Akhir

Dapat memanfaatkan pemprosesan teks lanjutan dan pengurusan memori Perls, atau pustaka analitik data Pythons dalam PostgreSQL boleh menjadi alat yang sangat berkuasa.

Mengalih tugas yang rumit kepada alatan yang lebih sesuai untuk mengendalikan tugas boleh mengurangkan overhed pada pangkalan data.

Seperti biasa, apabila menggunakan fungsi tersuai dan bahasa skrip luaran, ambil langkah berjaga-jaga untuk memastikan penggunaan selamat.

Atas ialah kandungan terperinci Mencipta Fungsi Tersuai Dalam PostgreSQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!