1CREATE TRANSFORM(7) PostgreSQL 12.2 Documentation CREATE TRANSFORM(7)
2
3
4
6 CREATE_TRANSFORM - define a new transform
7
9 CREATE [ OR REPLACE ] TRANSFORM FOR type_name LANGUAGE lang_name (
10 FROM SQL WITH FUNCTION from_sql_function_name [ (argument_type [, ...]) ],
11 TO SQL WITH FUNCTION to_sql_function_name [ (argument_type [, ...]) ]
12 );
13
15 CREATE TRANSFORM defines a new transform. CREATE OR REPLACE TRANSFORM
16 will either create a new transform, or replace an existing definition.
17
18 A transform specifies how to adapt a data type to a procedural
19 language. For example, when writing a function in PL/Python using the
20 hstore type, PL/Python has no prior knowledge how to present hstore
21 values in the Python environment. Language implementations usually
22 default to using the text representation, but that is inconvenient
23 when, for example, an associative array or a list would be more
24 appropriate.
25
26 A transform specifies two functions:
27
28 · A “from SQL” function that converts the type from the SQL
29 environment to the language. This function will be invoked on the
30 arguments of a function written in the language.
31
32 · A “to SQL” function that converts the type from the language to the
33 SQL environment. This function will be invoked on the return value
34 of a function written in the language.
35
36 It is not necessary to provide both of these functions. If one is not
37 specified, the language-specific default behavior will be used if
38 necessary. (To prevent a transformation in a certain direction from
39 happening at all, you could also write a transform function that always
40 errors out.)
41
42 To be able to create a transform, you must own and have USAGE privilege
43 on the type, have USAGE privilege on the language, and own and have
44 EXECUTE privilege on the from-SQL and to-SQL functions, if specified.
45
47 type_name
48 The name of the data type of the transform.
49
50 lang_name
51 The name of the language of the transform.
52
53 from_sql_function_name[(argument_type [, ...])]
54 The name of the function for converting the type from the SQL
55 environment to the language. It must take one argument of type
56 internal and return type internal. The actual argument will be of
57 the type for the transform, and the function should be coded as if
58 it were. (But it is not allowed to declare an SQL-level function
59 returning internal without at least one argument of type internal.)
60 The actual return value will be something specific to the language
61 implementation. If no argument list is specified, the function name
62 must be unique in its schema.
63
64 to_sql_function_name[(argument_type [, ...])]
65 The name of the function for converting the type from the language
66 to the SQL environment. It must take one argument of type internal
67 and return the type that is the type for the transform. The actual
68 argument value will be something specific to the language
69 implementation. If no argument list is specified, the function name
70 must be unique in its schema.
71
73 Use DROP TRANSFORM (DROP_TRANSFORM(7)) to remove transforms.
74
76 To create a transform for type hstore and language plpythonu, first set
77 up the type and the language:
78
79 CREATE TYPE hstore ...;
80
81 CREATE EXTENSION plpythonu;
82
83 Then create the necessary functions:
84
85 CREATE FUNCTION hstore_to_plpython(val internal) RETURNS internal
86 LANGUAGE C STRICT IMMUTABLE
87 AS ...;
88
89 CREATE FUNCTION plpython_to_hstore(val internal) RETURNS hstore
90 LANGUAGE C STRICT IMMUTABLE
91 AS ...;
92
93 And finally create the transform to connect them all together:
94
95 CREATE TRANSFORM FOR hstore LANGUAGE plpythonu (
96 FROM SQL WITH FUNCTION hstore_to_plpython(internal),
97 TO SQL WITH FUNCTION plpython_to_hstore(internal)
98 );
99
100 In practice, these commands would be wrapped up in an extension.
101
102 The contrib section contains a number of extensions that provide
103 transforms, which can serve as real-world examples.
104
106 This form of CREATE TRANSFORM is a PostgreSQL extension. There is a
107 CREATE TRANSFORM command in the SQL standard, but it is for adapting
108 data types to client languages. That usage is not supported by
109 PostgreSQL.
110
112 CREATE FUNCTION (CREATE_FUNCTION(7)), CREATE LANGUAGE
113 (CREATE_LANGUAGE(7)), CREATE TYPE (CREATE_TYPE(7)), DROP TRANSFORM
114 (DROP_TRANSFORM(7))
115
116
117
118PostgreSQL 12.2 2020 CREATE TRANSFORM(7)