PostgreSQL 11 preview - jsonb_plpython, jsonb_plperl 插件, PG类型jsonb与pyton,perl程序类型的相互转换

背景

PostgreSQL 支持多种内部存储过程语言，不同的语言对数据类型的支持，名字，定义可能不一样。为了让编程语言、数据库相互之间能够识别对方的类型，有一个MAPPING机制。

例如SQL类型，C类型 mapping如下：

https://www.postgresql.org/docs/devel/static/xfunc-c.html#XFUNC-C-TYPE-TABLE

其他编程语言又如何对应呢？

数据库现在还提供了一种接口，create TRANSFORM，允许用户自己定义编程语言内部类型、SQL类型相互之间的转换接口。

https://www.postgresql.org/docs/devel/static/sql-createtransform.html

定义数据库函数时，可以为数据库内部的类型设置对应的transform。

CREATE [ OR REPLACE ] FUNCTION  
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )  
    [ RETURNS rettype  
      | RETURNS TABLE ( column_name column_type [, ...] ) ]  
  { LANGUAGE lang_name  
    | TRANSFORM { FOR TYPE type_name } [, ... ]  
    | WINDOW  
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF  
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT  
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER  
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }  
    | COST execution_cost  
    | ROWS result_rows  
    | SET configuration_parameter { TO value | = value | FROM CURRENT }  
    | AS 'definition'  
    | AS 'obj_file', 'link_symbol'  
  } ...  

https://www.postgresql.org/docs/devel/static/sql-createfunction.html

TRANSFORM { FOR TYPE type_name } [, … ] }

Lists which transforms a call to the function should apply. Transforms convert between SQL types and language-specific data types; see CREATE TRANSFORM. Procedural language implementations usually have hardcoded knowledge of the built-in types, so those don’t need to be listed here. If a procedural language implementation does not know how to handle a type and no transform is supplied, it will fall back to a default behavior for converting data types, but this depends on the implementation.

jsonb_plpython, jsonb_plperl transform 插件

PostgreSQL 11 内部增加了两个transform，分别可以将SQL的jsonb类型映射到python, perl编程语言的内置类型中。

Add extension jsonb_plpython to transform JSONB to/from PL/Python types (Anthony Bykov)

Add extension jsonb_plperl to transform JSONB to/from PL/Perl types (Anthony Bykov)

The extensions for PL/Python are called jsonb_plpythonu, jsonb_plpython2u, and jsonb_plpython3u (see Section 46.1 for the PL/Python naming convention). If you use them, jsonb values are mapped to Python dictionaries, lists, and scalars, as appropriate.

这两个transform以插件出现，可以看到其MAKEFILE

# contrib/Makefile  
  
  
ifeq ($(with_perl),yes)  
SUBDIRS += hstore_plperl jsonb_plperl  
else  
ALWAYS_SUBDIRS += hstore_plperl jsonb_plperl  
endif  
  
ifeq ($(with_python),yes)  
SUBDIRS += hstore_plpython jsonb_plpython ltree_plpython  
else  
ALWAYS_SUBDIRS += hstore_plpython jsonb_plpython ltree_plpython  
endif  

我们可以到对应源码目录编译安装它们。

digoal’s 大量PostgreSQL文章入口