PostgreSQL में, जटिल समस्याओं को हल करने के लिए कस्टम फ़ंक्शन बनाए जा सकते हैं।

इन्हें डिफ़ॉल्ट PL/pgSQL स्क्रिप्टिंग भाषा का उपयोग करके लिखा जा सकता है, या इन्हें किसी अन्य स्क्रिप्टिंग भाषा में लिखा जा सकता है।

पायथन, पर्ल, टीसीएल और आर कुछ समर्थित स्क्रिप्टिंग भाषाएं हैं।

हालांकि पीएल/पीजीएसक्यूएल किसी भी पोस्टग्रेज इंस्टॉलेशन के साथ आता है, अन्य भाषाओं का उपयोग करने के लिए कुछ सेटअप की आवश्यकता होती है।

एक्सटेंशन इंस्टॉल करना

किसी एक्सटेंशन का उपयोग करने से पहले, एक्सटेंशन पैकेज को इंस्टॉल करना होगा।

उबंटू पर आप चलाएंगे:

पर्ल

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

पैकेज का नाम 'postgresql-plperl-14' PostgreSQL संस्करण 14 के लिए विशिष्ट है। यदि आप 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)

यह पंक्ति बताती है कि पोस्टग्रेज में एक फ़ंक्शन कैसे बनाया जाता है। CREATE OR REPLACE का उपयोग करके, यह नए फ़ंक्शन के साथ hello नाम से पहले से परिभाषित किसी भी फ़ंक्शन को ओवरराइट कर देगा।

क्रिएट फ़ंक्शन हैलो (नाम टेक्स्ट) का उपयोग करने से फ़ंक्शन को किसी मौजूदा फ़ंक्शन को ओवरराइट करने से रोका जा सकेगा और यदि फ़ंक्शन पहले से मौजूद है तो त्रुटि होगी।

RETURNS text AS $$

यह परिभाषित करता है कि पोस्टग्रेज डेटा प्रकार क्या लौटाया जाएगा, यह महत्वपूर्ण है कि निर्दिष्ट डेटा प्रकार पोस्टग्रेज द्वारा मान्यता प्राप्त प्रकार है। एक कस्टम डेटा प्रकार निर्दिष्ट किया जा सकता है, यदि कस्टम प्रकार पहले से ही परिभाषित है।

$$ कोड के एक ब्लॉक की शुरुआत और अंत को चिह्नित करने के लिए एक सीमांकक है। इस पंक्ति में यह कोड ब्लॉक की शुरुआत को चिह्नित कर रहा है।

$$ के आरंभ और अंत के बीच के सभी कोड पोस्टग्रेज़ द्वारा निष्पादित किए जाएंगे

$$ LANGUAGE plperl;

$$ स्क्रिप्ट के अंत को दर्शाता है और पोस्टग्रेज को बताता है कि स्क्रिप्ट को किस भाषा में पार्स किया जाना चाहिए।

फ़ंक्शन का उपयोग करना

फ़ंक्शन का उपयोग किसी भी अंतर्निहित पोस्टग्रेज़ फ़ंक्शन की तरह किया जा सकता है

SELECT 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 का उपयोग करने से फ़ंक्शन को चलाने के लिए आवश्यक पढ़ने के विशेषाधिकार मिल सकते हैं।

विश्वसनीय और अविश्वसनीय एक्सटेंशन

उपरोक्त एक्सटेंशन बनाते समय, plperl और plpython3 का उपयोग किया गया था। अधिकांश परिस्थितियों में ये उपयोग के लिए सही एक्सटेंशन हैं।

इन एक्सटेंशन की सर्वर फ़ाइल सिस्टम और सिस्टम कॉल तक सीमित पहुंच है।

एक्सटेंशन को u (plpython3u, plperlu)

ये अविश्वसनीय एक्सटेंशन हैं और सर्वर फ़ाइल सिस्टम तक अधिक पहुंच की अनुमति देते हैं।

ऐसे मामले हो सकते हैं जहां इसकी आवश्यकता होती है, उदाहरण के लिए, यदि आप पर्ल मॉड्यूल, पायथन लाइब्रेरीज़ का उपयोग करना चाहते हैं, या सिस्टम कॉल का उपयोग करना चाहते हैं।

उपरोक्त उदाहरण में, JSON आउटपुट एक स्ट्रिंग के रूप में उत्पन्न किया गया था, यदि वांछित हो, तो डेटा को JSON के रूप में एन्कोड करने के लिए पर्ल JSON मॉड्यूल का उपयोग किया जा सकता था। ऐसा करने के लिए JSON मॉड्यूल तक पहुंचने के लिए अविश्वसनीय एक्सटेंशन का उपयोग करने की आवश्यकता होगी।

अविश्वसनीय एक्सटेंशन का उपयोग न करने की सलाह दी जाती है, लेकिन यदि आवश्यक हो, तो सावधानी के साथ उपयोग करें और संभावित जोखिमों को समझें।

यदि पर्ल का उपयोग किया जा रहा है, तो अविश्वसनीय एक्सटेंशन उपयोग में होने पर पर्ल टेंट मोड में चलेगा।

अंतिम विचार

पर्ल्स उन्नत टेक्स्ट प्रोसेसिंग और मेमोरी प्रबंधन, या पोस्टग्रेएसक्यूएल के भीतर पायथन डेटा विश्लेषणात्मक लाइब्रेरी का लाभ उठाने में सक्षम होना वास्तव में एक शक्तिशाली उपकरण हो सकता है।

कार्य को संभालने के लिए अधिक उपयुक्त उपकरणों को जटिल कार्य सौंपने से डेटाबेस पर ओवरहेड कम हो सकता है।

हमेशा की तरह, कस्टम फ़ंक्शन और बाहरी स्क्रिप्टिंग भाषाओं का उपयोग करते समय, सुरक्षित उपयोग सुनिश्चित करने के लिए सावधानी बरतें।