标签
PostgreSQL , plpython , perl , jsonb , 存储过程 , 函数 , TRANSFORM , SQL类型与编程语言内部类型相互转换 , 增强 , 11
背景
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
我们可以到对应源码目录编译安装它们。
