Postgresql 中文操作指南

CREATE TRANSFORM

CREATE TRANSFORM — 定义新的转换

CREATE TRANSFORM — define a new transform

Synopsis

CREATE [ OR REPLACE ] TRANSFORM FOR type_name LANGUAGE lang_name (
    FROM SQL WITH FUNCTION from_sql_function_name [ (argument_type [, ...]) ],
    TO SQL WITH FUNCTION to_sql_function_name [ (argument_type [, ...]) ]
);

Description

CREATE TRANSFORM 定义新的转换。 CREATE OR REPLACE TRANSFORM 将创建新转换或替换现有定义。

CREATE TRANSFORM defines a new transform. CREATE OR REPLACE TRANSFORM will either create a new transform, or replace an existing definition.

变换指定如何将数据类型适配到一个过程语言中。例如，在使用 hstore 类型编写 PL/Python 语言的函数时，PL/Python 本身不具备如何展示 hstore 值的知识。语言实现一般都默认使用文本表示，但在联想数组或列表更合适的情况下，这种方式很繁琐。

A transform specifies how to adapt a data type to a procedural language. For example, when writing a function in PL/Python using the hstore type, PL/Python has no prior knowledge how to present hstore values in the Python environment. Language implementations usually default to using the text representation, but that is inconvenient when, for example, an associative array or a list would be more appropriate.

一个变换指定两个函数：

A transform specifies two functions:

两个函数都不一定要提供。如果未指定，必要时将使用针对语言的默认行为。（要阻止从某个方向进行变换，还可以编写一个始终出错的变换函数。）

It is not necessary to provide both of these functions. If one is not specified, the language-specific default behavior will be used if necessary. (To prevent a transformation in a certain direction from happening at all, you could also write a transform function that always errors out.)

要能够创建一个变换，你必须拥有该类型并具有 USAGE 权限，对该语言具有 USAGE 权限，并且如果指定了，拥有 from-SQL 和 to-SQL 函数并具有 EXECUTE 权限。

To be able to create a transform, you must own and have USAGE privilege on the type, have USAGE privilege on the language, and own and have EXECUTE privilege on the from-SQL and to-SQL functions, if specified.

Parameters

type_name
- The name of the data type of the transform.
lang_name
- The name of the language of the transform.
_from_sql_function_name[(argument_type [, …])]_
- The name of the function for converting the type from the SQL environment to the language. It must take one argument of type internal and return type internal. The actual argument will be of the type for the transform, and the function should be coded as if it were. (But it is not allowed to declare an SQL-level function returning internal without at least one argument of type internal.) The actual return value will be something specific to the language implementation. If no argument list is specified, the function name must be unique in its schema.
_to_sql_function_name[(argument_type [, …])]_
- The name of the function for converting the type from the language to the SQL environment. It must take one argument of type internal and return the type that is the type for the transform. The actual argument value will be something specific to the language implementation. If no argument list is specified, the function name must be unique in its schema.

Notes

用 DROP TRANSFORM 删除变换。

Use DROP TRANSFORM to remove transforms.

Examples

要为 hstore 类型和 plpython3u 语言创建一个变换，首先设置类型和语言：

To create a transform for type hstore and language plpython3u, first set up the type and the language:

CREATE TYPE hstore ...;

CREATE EXTENSION plpython3u;

然后创建必要的函数：

Then create the necessary functions:

CREATE FUNCTION hstore_to_plpython(val internal) RETURNS internal
LANGUAGE C STRICT IMMUTABLE
AS ...;

CREATE FUNCTION plpython_to_hstore(val internal) RETURNS hstore
LANGUAGE C STRICT IMMUTABLE
AS ...;

最后创建变换来将它们连接在一起：

And finally create the transform to connect them all together:

CREATE TRANSFORM FOR hstore LANGUAGE plpython3u (
    FROM SQL WITH FUNCTION hstore_to_plpython(internal),
    TO SQL WITH FUNCTION plpython_to_hstore(internal)
);

在实践中，这些命令会被包装在一个扩展中。

In practice, these commands would be wrapped up in an extension.

contrib 部分包含许多提供变换的扩展，这些扩展可以作为实际示例。

The contrib section contains a number of extensions that provide transforms, which can serve as real-world examples.

Compatibility

这种形式的 CREATE TRANSFORM 是 PostgreSQL 扩展。SQL 标准中有一个 CREATE TRANSFORM 命令，但它用于将数据类型适配到客户端语言。PostgreSQL 不支持这样的用法。

This form of CREATE TRANSFORM is a PostgreSQL extension. There is a CREATE TRANSFORM command in the SQL standard, but it is for adapting data types to client languages. That usage is not supported by PostgreSQL.