CREATE TRANSFORM
Defines 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
defines a new transform. CREATE OR REPLACE TRANSFORM
will either create a new transform, or replace an existing definition.
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:
-
A "from SQL" function that converts the type from the SQL environment to the language. This function will be invoked on the arguments of a function written in the language.
-
A "to SQL" function that converts the type from the language to the SQL environment. This function will be invoked on the return value of a function written in the language.
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.)
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 a single 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. (An SQL-level function returning internal
must declare 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 a single 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.