Debian Codegraph @

doc/src/sgml/ref/create_transform.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_transform.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createtransform">
7 <indexterm zone="sql-createtransform">
8 <primary>CREATE TRANSFORM</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE TRANSFORM</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE TRANSFORM</refname>
19 <refpurpose>define a new transform</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE [ OR REPLACE ] TRANSFORM FOR <replaceable>type_name</replaceable> LANGUAGE <replaceable>lang_name</replaceable> (
25 FROM SQL WITH FUNCTION <replaceable>from_sql_function_name</replaceable> [ (<replaceable>argument_type</replaceable> [, ...]) ],
26 TO SQL WITH FUNCTION <replaceable>to_sql_function_name</replaceable> [ (<replaceable>argument_type</replaceable> [, ...]) ]
27 );
28 </synopsis>
29 </refsynopsisdiv>
30
31 <refsect1 id="sql-createtransform-description">
32 <title>Description</title>
33
34 <para>
35 <command>CREATE TRANSFORM</command> defines a new transform.
36 <command>CREATE OR REPLACE TRANSFORM</command> will either create a new
37 transform, or replace an existing definition.
38 </para>
39
40 <para>
41 A transform specifies how to adapt a data type to a procedural language.
42 For example, when writing a function in PL/Python using
43 the <type>hstore</type> type, PL/Python has no prior knowledge how to
44 present <type>hstore</type> values in the Python environment. Language
45 implementations usually default to using the text representation, but that
46 is inconvenient when, for example, an associative array or a list would be
47 more appropriate.
48 </para>
49
50 <para>
51 A transform specifies two functions:
52 <itemizedlist>
53 <listitem>
54 <para>
55 A <quote>from SQL</quote> function that converts the type from the SQL
56 environment to the language. This function will be invoked on the
57 arguments of a function written in the language.
58 </para>
59 </listitem>
60
61 <listitem>
62 <para>
63 A <quote>to SQL</quote> function that converts the type from the
64 language to the SQL environment. This function will be invoked on the
65 return value of a function written in the language.
66 </para>
67 </listitem>
68 </itemizedlist>
69 It is not necessary to provide both of these functions. If one is not
70 specified, the language-specific default behavior will be used if
71 necessary. (To prevent a transformation in a certain direction from
72 happening at all, you could also write a transform function that always
73 errors out.)
74 </para>
75
76 <para>
77 To be able to create a transform, you must own and
78 have <literal>USAGE</literal> privilege on the type, have
79 <literal>USAGE</literal> privilege on the language, and own and
80 have <literal>EXECUTE</literal> privilege on the from-SQL and to-SQL
81 functions, if specified.
82 </para>
83 </refsect1>
84
85 <refsect1>
86 <title>Parameters</title>
87
88 <variablelist>
89 <varlistentry>
90 <term><replaceable>type_name</replaceable></term>
91
92 <listitem>
93 <para>
94 The name of the data type of the transform.
95 </para>
96 </listitem>
97 </varlistentry>
98
99 <varlistentry>
100 <term><replaceable>lang_name</replaceable></term>
101
102 <listitem>
103 <para>
104 The name of the language of the transform.
105 </para>
106 </listitem>
107 </varlistentry>
108
109 <varlistentry>
110 <term><literal><replaceable>from_sql_function_name</replaceable>[(<replaceable>argument_type</replaceable> [, ...])]</literal></term>
111
112 <listitem>
113 <para>
114 The name of the function for converting the type from the SQL
115 environment to the language. It must take one argument of
116 type <type>internal</type> and return type <type>internal</type>. The
117 actual argument will be of the type for the transform, and the function
118 should be coded as if it were. (But it is not allowed to declare an
119 SQL-level function returning <type>internal</type> without at
120 least one argument of type <type>internal</type>.) The actual return
121 value will be something specific to the language implementation.
122 If no argument list is specified, the function name must be unique in
123 its schema.
124 </para>
125 </listitem>
126 </varlistentry>
127
128 <varlistentry>
129 <term><literal><replaceable>to_sql_function_name</replaceable>[(<replaceable>argument_type</replaceable> [, ...])]</literal></term>
130
131 <listitem>
132 <para>
133 The name of the function for converting the type from the language to
134 the SQL environment. It must take one argument of type
135 <type>internal</type> and return the type that is the type for the
136 transform. The actual argument value will be something specific to the
137 language implementation.
138 If no argument list is specified, the function name must be unique in
139 its schema.
140 </para>
141 </listitem>
142 </varlistentry>
143 </variablelist>
144 </refsect1>
145
146 <refsect1 id="sql-createtransform-notes">
147 <title>Notes</title>
148
149 <para>
150 Use <link linkend="sql-droptransform"><command>DROP TRANSFORM</command></link> to remove transforms.
151 </para>
152 </refsect1>
153
154 <refsect1 id="sql-createtransform-examples">
155 <title>Examples</title>
156
157 <para>
158 To create a transform for type <type>hstore</type> and language
159 <literal>plpython3u</literal>, first set up the type and the language:
160 <programlisting>
161 CREATE TYPE hstore ...;
162
163 CREATE EXTENSION plpython3u;
164 </programlisting>
165 Then create the necessary functions:
166 <programlisting>
167 CREATE FUNCTION hstore_to_plpython(val internal) RETURNS internal
168 LANGUAGE C STRICT IMMUTABLE
169 AS ...;
170
171 CREATE FUNCTION plpython_to_hstore(val internal) RETURNS hstore
172 LANGUAGE C STRICT IMMUTABLE
173 AS ...;
174 </programlisting>
175 And finally create the transform to connect them all together:
176 <programlisting>
177 CREATE TRANSFORM FOR hstore LANGUAGE plpython3u (
178 FROM SQL WITH FUNCTION hstore_to_plpython(internal),
179 TO SQL WITH FUNCTION plpython_to_hstore(internal)
180 );
181 </programlisting>
182 In practice, these commands would be wrapped up in an extension.
183 </para>
184
185 <para>
186 The <filename>contrib</filename> section contains a number of extensions
187 that provide transforms, which can serve as real-world examples.
188 </para>
189 </refsect1>
190
191 <refsect1 id="sql-createtransform-compat">
192 <title>Compatibility</title>
193
194 <para>
195 This form of <command>CREATE TRANSFORM</command> is a
196 <productname>PostgreSQL</productname> extension. There is a <command>CREATE
197 TRANSFORM</command> command in the <acronym>SQL</acronym> standard, but it
198 is for adapting data types to client languages. That usage is not supported
199 by <productname>PostgreSQL</productname>.
200 </para>
201 </refsect1>
202
203 <refsect1 id="sql-createtransform-seealso">
204 <title>See Also</title>
205
206 <para>
207 <xref linkend="sql-createfunction"/>,
208 <xref linkend="sql-createlanguage"/>,
209 <xref linkend="sql-createtype"/>,
210 <xref linkend="sql-droptransform"/>
211 </para>
212 </refsect1>
213
214 </refentry>