في PostgreSQL، يمكن إنشاء وظائف مخصصة لحل المشكلات المعقدة.

يمكن كتابتها باستخدام لغة البرمجة النصية PL/pgSQL الافتراضية، أو يمكن كتابتها بلغة برمجة نصية أخرى.

Python، وPerl، وTcl، وR هي بعض لغات البرمجة النصية المدعومة.

بينما يأتي PL/pgSQL مع أي تثبيت Postgres، فإن استخدام لغات أخرى يتطلب بعض الإعداد.

تثبيت الامتداد

قبل أن يتم استخدام الامتداد، يجب تثبيت حزمة الامتداد.

في Ubuntu يمكنك تشغيل:

بيرل

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

اسم الحزمة 'postgresql-plperl-14' خاص بالإصدار 14 من PostgreSQL. إذا كنت تستخدم إصدارًا مختلفًا من PostgreSQL، فستحتاج إلى تغيير رقم الإصدار في اسم الحزمة ليتوافق مع إصدار PostgreSQL المثبت لديك.

بايثون 3

sudo apt-get install postgresql-plpython3-14

تفعيل التمديد

لتنشيط الامتداد في PostgreSQL، يجب تحديد الامتداد باستخدام عبارة CREATE EXTENSION.

بيرل

CREATE EXTENSION plperl;

بايثون

CREATE EXTENSION plpython3;

مرحبا بالعالم مثال

بمجرد إنشاء الامتداد، يمكن إنشاء وظيفة مخصصة باستخدام الامتداد.

بيرل

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

بايثون

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

تقسيم هذا سطرًا تلو الآخر

CREATE OR REPLACE FUNCTION hello(name text)

هذا السطر هو كيفية إنشاء وظيفة في Postgres. باستخدام "إنشاء أو استبدال"، سيتم استبدال أي وظيفة تم تعريفها بالفعل بالاسم hello بالوظيفة الجديدة.

استخدام إنشاء وظيفة مرحبًا (نص الاسم) سيمنع الوظيفة من الكتابة فوق وظيفة موجودة وسيظهر خطأ إذا كانت الوظيفة موجودة بالفعل.

RETURNS text AS $$

يحدد هذا نوع بيانات Postgres الذي سيتم إرجاعه، ومن المهم أن يكون نوع البيانات المحدد هو النوع الذي يتعرف عليه Postgres. يمكن تحديد نوع بيانات مخصص، إذا كان النوع المخصص محددًا بالفعل.

$$ هو محدد لتحديد بداية ونهاية كتلة من التعليمات البرمجية. في هذا السطر يمثل بداية كتلة التعليمات البرمجية.

سيتم تنفيذ جميع التعليمات البرمجية بين البداية والنهاية $$ بواسطة Postgres

$$ LANGUAGE plperl;

$$ يشير إلى نهاية البرنامج النصي ويخبر Postgres باللغة التي يجب تحليل البرنامج النصي بها.

باستخدام الوظيفة

يمكن استخدام الوظائف مثل أي وظيفة Postgres مدمجة

SELECT hello('world');

سيؤدي هذا إلى إرجاع عمود بقيمة Hello World!

يمكن أن تكون الوظائف جزءًا من استعلامات أكثر تعقيدًا:

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

مثال أكثر تعقيدا

إليك مثال على دالة تقبل النص من حقل وتقوم بإرجاع عدد الكلمات.

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;

يؤدي هذا إلى إرجاع نتيجة بتنسيق JSON مع عدد الكلمات.

يمكننا إضافة إحصائيات أكثر تفصيلاً إلى الوظيفة.

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;

الآن عندما نستخدمها في استعلام

SELECT word_count(text_field) word_count FROM table

سيُرجع JSON مثل

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

اعتبارات أمنية

عند استخدام الوظائف المخصصة أو لغات البرمجة النصية الخارجية، هناك اعتبارات أمنية إضافية يجب أخذها في الاعتبار. يمكن أن يكون الأمر بمثابة شعوذة للحصول على التوازن الصحيح بين سهولة الاستخدام والأمان.

محدد الأمان مقابل مستحضر الأمان

في الوظيفة السابقة، تمت إضافة خيار SECURITY DEFINER إلى بيان إنشاء الوظيفة.

من المهم التفكير في الطريقة التي تريد بها تشغيل الوظيفة من وجهة نظر أمنية.

السلوك الافتراضي هو استخدام SECURITY INVOKER. سيؤدي هذا إلى تشغيل الوظيفة بامتيازات المستخدم الذي يقوم بتشغيل الوظيفة.

يوفر محدد الأمان مزيدًا من التحكم في الامتيازات الممنوحة للوظيفة. باستخدام هذا الوضع، سيتم تشغيل الوظيفة بامتيازات المستخدم الذي أنشأ الوظيفة.

يمكن أن يكون هذا جيدًا وسيئًا، إذا تم إنشاء وظيفة بواسطة مستخدم بامتيازات محدودة، فلن يكون هناك ضرر يذكر لقاعدة البيانات.

إذا تم إنشاء الوظيفة بواسطة مستخدم يتمتع بامتيازات وصول عالية، فسيتم تشغيل الوظيفة بنفس الامتيازات. اعتمادًا على نوع الوظيفة، قد يسمح ذلك للمستخدم بتشغيل الوظيفة بامتيازات مفتوحة أكثر مما تم منحه له.

هناك أوقات يكون فيها ذلك مفيدًا، على سبيل المثال، إذا لم يكن لدى المستخدم امتيازات القراءة لجدول، ولكن داخل الوظيفة، فإن القراءة مطلوبة، باستخدام SECURITY DEFINER يمكن أن يسمح بامتيازات القراءة المطلوبة لتشغيل الوظيفة.

ملحقات موثوقة وغير موثوقة

عند إنشاء الامتدادات أعلاه، تم استخدام plperl وplpython3. في معظم الحالات، هذه هي الامتدادات المناسبة للاستخدام.

هذه الملحقات لها وصول محدود إلى نظام ملفات الخوادم ومكالمات النظام.

يمكن أيضًا إنشاء الامتدادات باستخدام u (plpython3u، plperlu)

هذه امتدادات غير موثوقة وتسمح بوصول أكبر إلى نظام ملفات الخوادم.

قد تكون هناك حالات حيث يكون ذلك مطلوبًا، على سبيل المثال، إذا كنت تريد استخدام وحدات Perl، أو مكتبات Python، أو استخدام استدعاءات النظام.

في المثال أعلاه، تم إنشاء مخرجات JSON كسلسلة، إذا رغبت في ذلك، كان من الممكن استخدام وحدة Perl JSON لترميز البيانات كـ JSON. للقيام بذلك يتطلب استخدام الامتداد غير الموثوق به للوصول إلى وحدة JSON.

يُنصح بعدم استخدام الامتدادات غير الموثوقة، ولكن إذا لزم الأمر، استخدمها بحذر وافهم المخاطر المحتملة.

إذا تم استخدام Perl، فسيتم تشغيل Perl في وضع ملوث عندما يكون الامتداد غير الموثوق به قيد الاستخدام.

افكار اخيرة

يمكن أن تكون القدرة على الاستفادة من معالجة النصوص المتقدمة وإدارة الذاكرة من Perls، أو مكتبات تحليل بيانات Pythons داخل PostgreSQL أداة قوية حقًا.

يمكن أن يؤدي تمرير المهام المعقدة إلى أدوات أكثر ملاءمة للتعامل مع المهمة إلى تقليل الحمل على قاعدة البيانات.

كما هو الحال دائمًا، عند استخدام الوظائف المخصصة ولغات البرمجة النصية الخارجية، اتخذ الاحتياطات اللازمة لضمان الاستخدام الآمن.