PostgreSQL: Server-side programming in the human language (PL / Perl, PL / Python, PL / v8)

• Then, when you need to work with complex structures, with algorithms: traversing trees, for example, or when HTML or XML parsing is required, especially when extracting them from archives;
• When you need to dynamically generate complex SQL (reports, ORM). On PL / pgSQL, it’s not only inconvenient, but will also work slower in some cases;
• Perl Python, C/C++, Perl Python . . , Oracle. , Postgres . Perl Python .
• — . , , untrusted- ( — . ), Perlu Python(3)u, PL/V8. Postgres , , FDW, , . . !
• And one more thing: if you are going to write something in C, then you can make a prototype in these languages ​​that are more adapted to rapid development.

How to embed a language in Postgres

• HANDLER - a call handler that will execute a function in the language (this is a required part);
• INLINE - anonymous block handler (if you want the language to support anonymous blocks);
• VALIDATOR - code verification function when creating a function (if you want this verification to be done).

“Languages ​​out of the box” and other languages

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       |

Standards

Trusted and Untrusted Languages

PL / pgSQL vs PL / *

PL / Perl

PL / Python

PL / V8

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() );
$$;

Comparison of PL / Perl vs PL / Python vs PL / V8 in practice

Hello World!

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

Work with the base

How and in what form do we get data from the database

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

Shielding

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'

Performance

\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

Performance with 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

Computing performance

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

Performance functions for sorting

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';

Memory

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;
$$;

Parameters

• whole;
• an array;
• bytea and
• 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)

• When passing parameters to a function;
• When returning a function value;
• When passing parameters to an SPI call inside a function;
• Upon receipt of the result of the SPI call inside the function.

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)

Work with infinity

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

Passing parameters to prepared requests

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();
$$;

Work with cursor

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();

PL / V8: Quick access to features

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

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

Transactions

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) {
...
}

Conclusion