PostgreSQL: मानव भाषा में सर्वर-साइड प्रोग्रामिंग (पीएल / पर्ल, पीएल / पायथन, पीएल / वी 8)

• फिर, जब आपको एल्गोरिदम के साथ जटिल संरचनाओं के साथ काम करने की आवश्यकता होती है: पेड़ों को पीछे हटाना, उदाहरण के लिए, या जब HTML या XML पार्सिंग की आवश्यकता होती है, खासकर जब उन्हें अभिलेखागार से निकालते हैं;
• जब आपको जटिल एसक्यूएल (रिपोर्ट, ओआरएम) को गतिशील रूप से उत्पन्न करने की आवश्यकता होती है। पीएल / पीजीसीक्यूएल पर, यह न केवल असुविधाजनक है, बल्कि कुछ मामलों में धीमी गति से भी काम करेगा;
• Perl Python, C/C++, Perl Python . . , Oracle. , Postgres . Perl Python .
• — . , , untrusted- ( — . ), Perlu Python(3)u, PL/V8. Postgres , , FDW, , . . !
• और एक और बात: यदि आप सी में कुछ लिखने जा रहे हैं, तो आप इन भाषाओं में एक प्रोटोटाइप बना सकते हैं जो तेजी से विकास के लिए अधिक अनुकूलित हैं।

Postgres में किसी भाषा को एम्बेड कैसे करें

• हैंडलर - एक कॉल हैंडलर जो भाषा में एक फ़ंक्शन निष्पादित करेगा (यह एक आवश्यक हिस्सा है);
• ऑनलाइन - अनाम ब्लॉक हैंडलर (यदि आप अनाम ब्लॉकों का समर्थन करने के लिए भाषा चाहते हैं);
• VALIDATOR - फ़ंक्शन बनाते समय कोड सत्यापन फ़ंक्शन (यदि आप चाहते हैं कि यह सत्यापन किया जाए)।

"बॉक्स से बाहर की भाषाएँ" और अन्य भाषाएँ

CREATE EXTENSION plperl;

test_langs=# \x
test_langs=# \dL+
List of languages
-[ RECORD 1 ]-----+---------------------------------
Name              | plperl
Owner             | postgres
Trusted           | t
Internal language | f
Call handler      | plperl_call_handler()
Validator         | plperl_validator(oid)
Inline handler    | plperl_inline_handler(internal)
Access privileges |
Description       | PL/Perl procedural language
-[ RECORD 2 ]-----+---------------------------------
Name              | plpgsql
Owner             | postgres
Trusted           | t
Internal language | f
Call handler      | plpgsql_call_handler()
Validator         | plpgsql_validator(oid)
Inline handler    | plpgsql_inline_handler(internal)
Access privileges |
Description       | PL/pgSQL procedural language
[ RECORD 3 ]-----+---------------------------------
Name              | plv8
Owner             | postgres
Trusted           | t
Internal language | f
Call handler      | plv8_call_handler()
Validator         | plv8_call_validator(oid)
Inline handler    | plv8_inline_handler(internal)
Access privileges |
Description       |

मानक

विश्वसनीय और अविश्वसनीय भाषाएँ

PL / pgSQL बनाम PL / *

पीएल / पर्ल

पीएल / पायथन

पीएल / वी 8

plv8.start_proc=my_init # ( PL/V8-)

CREATE OR REPLACE FUNCTION my_init()
RETURNS void LANGUAGE plv8 AS $$
     this.get_57 = function() { return 57; }; //   
     this.pi_square = 9.8696044;  //   
$$;
SET plv8.start_proc = 'my_init';
DO LANGUAGE plv8 $$
     plv8.elog(NOTICE, pi_square, get_57() );
$$;

व्यवहार में PL / पर्ल बनाम PL / पायथन बनाम PL / V8 की तुलना

नमस्ते दुनिया!

DO $$
     elog(NOTICE,"Hello World! $]");
$$ LANGUAGE plperl;

NOTICE:  Hello World!
DO

DO $$
     import sys
     plpy.notice('Hello World! ' , hint=" ", detail=sys.version_info)
$$ LANGUAGE plpython3u;

NOTICE:  Hello World! 
DETAIL:  sys.version_info(major=3, minor=6, micro=9, releaselevel='final', serial=0)
HINT:   
DO

DO $$
     plv8.elog(NOTICE, 'Hello World!', plv8.version);
$$ LANGUAGE plv8;

NOTICE:  Hello World! 2.3.14
DO

आधार के साथ काम करें

हम डेटाबेस से डेटा कैसे और किस रूप में प्राप्त करते हैं

DO $$ 
     warn Data::Dumper::Dumper(
          spi_exec_query('SELECT 57 AS x')
     )
$$ LANGUAGE plperl;

WARNING:  $VAR1 = {
          'rows' => [
                    {
                      'x' => '57'
                    }
                  ],
          'processed' => 1,
          'status' => 'SPI_OK_SELECT'
        };

DO $$ 
     plpy.notice(
          plpy.execute('SELECT 57 AS x')
     )
$$ LANGUAGE plpython3u;

NOTICE:  <PLyResult status=5 nrows=1 rows=[{'x': 57}]>
DO

DO $$ 
     plpy.notice(
          plpy.execute('SELECT 57 AS x')[0]['x']
      )
$$ LANGUAGE plpython3u;

NOTICE:  57
DO

DO $$ 
     plv8.elog(NOTICE, JSON.stringify(
          plv8.execute('SELECT 57 as x'))
     );
$$ LANGUAGE plv8;

NOTICE:  [{"x":57}]
DO

परिरक्षण

DO $$
     warn "macy's";
     warn quote_literal("macy's");
$$ LANGUAGE plperl;

WARNING:  macy's at line 2.
WARNING:  'macy''s' at line 3.
DO

DO $$
     warn encode_typed_literal(
          ["", " "], "text[]"
     );
$$ LANGUAGE plperl;

WARNING:  {," "} at line 2.
DO

DO $$ plpy.notice(
     plpy.quote_literal("Macy's"));
$$ LANGUAGE plpython3u;

NOTICE:  'Macy''s'
DO

DO $$
    plv8.elog(NOTICE, plv8.quote_nullable("Macy's"));
$$ LANGUAGE plv8;

NOTICE:  'Macy''s'

प्रदर्शन

\timing

SELECT count(*) FROM pg_class;
0.5 ms

DO $$
     DECLARE a int;
     BEGIN
          SELECT count(*) INTO a FROM pg_class;
     END;
$$ LANGUAGE plpgsql;
0.7 ms

DO $$
     my $x = spi_exec_query('SELECT count(*) FROM pg_class');
$$ LANGUAGE plperl;
0.7 ms

DO $$
     x = plpy.execute('SELECT count(*) FROM pg_class');
$$ LANGUAGE plpythonu;
0.8 ms

DO $$
     x = plpy.execute('SELECT count(*) FROM pg_class');
$$ LANGUAGE plpython3u;
0.9ms

DO $$
     var x = plv8.execute('SELECT count(*) FROM pg_class');
$$ LANGUAGE plv8 ;
0.9 ms

DO $$
     DECLARE a int; i int;
     BEGIN FOR i IN 0..999999 LOOP
          SELECT count(*) INTO a FROM pg_class;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
53s

DO $$
     for (0..999999) {
          spi_exec_query('SELECT count(*) FROM pg_class');
     }
$$ LANGUAGE plperl;
102s

DO $$
     for i in range (0,1000000) :
          plpy.execute('SELECT count(*) FROM pg_class')
$$ LANGUAGE plpython3u;
98s

DO $$
     for(var i=0;i<1000;i++)
          plv8.execute('SELECT count(*) FROM pg_class');
$$ LANGUAGE plv8;
100ms

PREPARE के साथ प्रदर्शन

DO $$
     my $h = spi_prepare('SELECT count(*) FROM pg_class');
     for (0..999999) {
          spi_exec_prepared($h);
     }
     spi_freeplan($h);
$$ LANGUAGE plperl;
60s

DO $$
     h = plpy.prepare('SELECT count(*) FROM pg_class')
     for i in range (0,1000000): plpy.execute(h)
$$ LANGUAGE plpython3u;
62s

DO $$
     var h=plv8.prepare('SELECT count(*) FROM pg_class');
     for(var i=0;i<1000;i++) h.execute();
$$ LANGUAGE plv8;
53ms

कम्प्यूटिंग प्रदर्शन

DO $$
     DECLARE i bigint; a bigint;
     BEGIN a=0;
     FOR i IN 0..1000000 LOOP
          a=a+i*i::bigint;
     END LOOP;
END;
$$ LANGUAGE plpgsql;
280ms

DO $$
     my $a=0;
     for my $i (0..1000000) { $a+=$i*$i; };
     warn $a;
$$ LANGUAGE plperl;
63ms

DO $$
a=0
for i in range(1,1000001): a=a+i*i
$$ LANGUAGE plpython3u;
73ms

DO $$
     var a=0;
     for(var i=0;i<=1000000;i++) a+=i*i;
     plv8.elog(NOTICE, a);
$$ language plv8;
7.5ms

∑ = n*(n+1)*(2n+1)/6

DO LANGUAGE plv8 $$
plv8.elog(NOTICE, parseInt(33333383333312755033)) $$;

NOTICE:
33333383333312754000

छँटाई के लिए प्रदर्शन कार्य

1
2
3
4-5
6
6A
6
11
12

0000000001
0000000002
0000000003
0000000004-5
0000000006
0000000006A
0000000006
0000000011
0000000012

CREATE OR REPLACE FUNCTION nsort(text) RETURNS text 
   LANGUAGE PLPERL IMMUTABLE AS $$
    my $x = shift;
    return ($x =~ /^\s*(\d+)(.*)$/)
        ? sprintf("%010d", $1).$2
        : $x;
$$;

CREATE OR REPLACE FUNCTION _nsort(x text) RETURNS text
     LANGUAGE SQL  IMMUTABLE  AS $$
 WITH y AS (
    SELECT regexp_match(x,'^\s*(\d*)(.*)$') as z
 )
 SELECT CASE WHEN z[1] = '' THEN x ELSE lpad(z[1],10,'0') || z[2] END FROM y;
$$;

CREATE OR REPLACE FUNCTION py_nsort(x text) RETURNS text 
   LANGUAGE plpython2u IMMUTABLE AS $$
import re
r = re.match('^\s*(\d+)(.*)$', x)
return x if r == None else ('%010d' % int(r.group(1))) + r.group(2)
$$;

CREATE OR REPLACE FUNCTION js_nsort(x text) RETURNS text 
   LANGUAGE plv8 IMMUTABLE AS $$
var m = x.match(/^\s*(\d+)(.*)$/);
if(m) { return m[1].padStart(10-m[1].length,'0') + m[2]; }
else { return x; } 
$$;

set work_mem = '20MB';

याद

CREATE OR REPLACE function cr()
RETURNS int LANGUAGE plperl AS
$$
     return spi_exec_query(
           'SELECT count(*) FROM pg_class'
     )->{rows}->[0]->{count};
$$;

CREATE OR REPLACE function cr()
RETURNS int LANGUAGE plperl AS
$$
     my $h = spi_prepare(
          'SELECT count(*) FROM pg_class'
     );
     return spi_exec_prepared($h)->{rows}->[0]->{count};
$$;

CREATE OR REPLACE function cr()
RETURNS int LANGUAGE plperl AS
$$
     my $h = spi_prepare(
          'select count(*) from pg_class'
     );
     my $res = spi_exec_prepared($h)->{rows}->[0]->{count};
     spi_freeplan($h);
     return $res;
$$;

CREATE OR REPLACE function cr3() RETURNS int
LANGUAGE plpythonu as
$$
     return plpy.execute(
           'select count(*) from pg_class'
     )[0]['count']
$$;

CREATE OR REPLACE FUNCTION crq() RETURNS int
LANGUAGE plv8 AS
$$
     return plv8.execute(
          'select count(*) from pg_class‘
     )[0].count;
$$;

CREATE OR REPLACE FUNCTION crq() RETURNS int
LANGUAGE plv8 AS
$$
     var h = plv8.prepare(
          'select count(*) from pg_class'
     );
     return h.execute()[0].count;
$$;

CREATE OR REPLACE FUNCTION crq() RETURNS int
LANGUAGE plv8 AS
$$
     var h = plv8.prepare(
          'select count(*) from pg_class'
     );
     var r = h.execute()[0].count;
     h.free();
     return r;
$$;

पैरामीटर

• पूरा का पूरा;
• एक सरणी;
• बाइटा और
• jsonb

CREATE OR REPLACE FUNCTION crq(a int, b
bytea, c int[], d jsonb ) RETURNS void
LANGUAGE plperl AS
$$
    warn Dumper(@_);
$$;

SELECT crq(1,'abcd', ARRAY[1,2,3],'{"a":2,"b":3}');

WARNING:  $VAR1 = '1';
$VAR2 = '\\x61626364';
$VAR3 = bless( {
                 'array' => [
                              '1',
                              '2',
                              '3'
                            ],
                 'typeoid' => 1007
               }, 'PostgreSQL::InServer::ARRAY' );
$VAR4 = '{"a": 2, "b": 3}';
 crq 
-----
(1 row)

• जब एक फ़ंक्शन के लिए मापदंडों को पारित करना;
• जब एक फ़ंक्शन मान लौटाते हैं;
• एक फ़ंक्शन के अंदर एक एसपीआई कॉल के मापदंडों को पारित करते समय;
• फ़ंक्शन के अंदर एसपीआई कॉल के परिणाम प्राप्त होने पर।

CREATE EXTENSION jsonb_plperl;
CREATE OR REPLACE FUNCTION crq2(d jsonb)
RETURNS void LANGUAGE plperl
TRANSFORM FOR TYPE jsonb AS $$
     warn Dumper(@_);
$$;

SELECT crq2( '{"a":2,"b":3}');

WARNING:  $VAR1 = {
          'a' => '2',
          'b' => '3'
        };
 crq2 
------
(1 row)

CREATE EXTENSION jsonb_plpython3u;
CREATE OR REPLACE FUNCTION pdump(a int, b bytea, c int[], d jsonb ) RETURNS void
LANGUAGE plpython3u
TRANSFORM FOR TYPE jsonb AS $$
      plpy.warning(a,b,c,d)
$$;

SELECT pdump(1,'abcd', ARRAY[1,2,3],'{"a":2,"b":3}');

WARNING:  (1, b'abcd', [1, 2, 3], {'a': Decimal('2'), 'b': Decimal('3')})
 pdump 
-------
(1 row)

CREATE OR REPLACE FUNCTION jsdump(a int, b bytea, c int[], d jsonb) RETURNS void
LANGUAGE plv8 AS $$
     plv8.elog(WARNING,a,b,c,d)
$$;

SELECT jsdump(1,'abcd', ARRAY[1,2,3],'{"a":2,"b":3}');

WARNING:  1 97,98,99,100 1,2,3 [object Object]
jsdump 
-------
(1 row)

अनंत के साथ काम करें

import numpy as nm
a = nm.inf
b = -nm.inf
print(a, b)

inf -inf

perl -e 'print 1 * "inf"'

Inf

perl -e 'print 1/"inf"'

0

तैयार अनुरोधों के लिए पैरामीटर पारित करना

DO LANGUAGE plperl $$
     my $h= spi_prepare('SELECT * FROM pg_class WHERE
          relname ~ $1', 'text' );                     #   
     warn Dumper(spi_exec_prepared($h, 'pg_language')); #   
     spi_freeplan($h);
$$;

DO LANGUAGE plpython3u $$
     h= plpy.prepare('SELECT relname FROM pg_class WHERE relname ~ $1', ['text'] )
     plpy.notice(.execute (['pg_language']))
$$;

DO LANGUAGE plv8 $$
    var h= plv8.prepare('SELECT relname FROM pg_class WHERE relname ~ $1', ['text'] );
    plv8.elog(NOTICE, h.execute (['pg_language']));
    h.free();
$$;

कर्सर के साथ काम करें

DO LANGUAGE plperl $$
    my $cursor = spi_query('SELECT * FROM pg_class');
    my $row;
    while(defined($row = spi_fetchrow($cursor))) {
         warn $row->{relname};
    }
$$;

WARNING:  pg_statistic at line 5.
WARNING:  pg_toast_2604 at line 5.
WARNING:  pg_toast_2604_index at line 5.
WARNING:  pg_toast_2606 at line 5.
WARNING:  pg_toast_2606_index at line 5.
WARNING:  pg_toast_2609 at line 5.
WARNING:  pg_toast_2609_index at line 5.
...

h = plpy.prepare('SELECT ...');
cursor = plpy.cursor(h);
for row in cursor:
...
cursor.close() //  

var h = plv.prepare('SELECT ...');
var cursor = h.cursor();
var row;
while(row = cursor.fetch()) {
...
}
cursor.close();
h.free();

पीएल / वी 8: सुविधाओं तक त्वरित पहुंच

ERROR:  Error: more than one function named "jsdump"

DO LANGUAGE plv8 $$
plv8.find_function('jsdump')(1, 'abc');
$$;

लेन-देन

Subtransaction

try:
     with plpy.subtransaction():
          plpy.execute("...")
          plpy.execute("...")
except plpy.SPIError, e:
. . .
else:
. . .

try {
plv8.subtransaction(function() {
plv8.execute('UPDATE...');
plv8.execute('UPDATE...');
});
}
catch(e) {
...
}

निष्कर्ष