1<!--
2doc/src/sgml/ref/create_transform.sgml
3PostgreSQL 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>
24CREATE [ 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 <xref linkend="sql-droptransform"/> 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>plpythonu</literal>, first set up the type and the language:
160<programlisting>
161CREATE TYPE hstore ...;
162
163CREATE EXTENSION plpythonu;
164</programlisting>
165   Then create the necessary functions:
166<programlisting>
167CREATE FUNCTION hstore_to_plpython(val internal) RETURNS internal
168LANGUAGE C STRICT IMMUTABLE
169AS ...;
170
171CREATE FUNCTION plpython_to_hstore(val internal) RETURNS hstore
172LANGUAGE C STRICT IMMUTABLE
173AS ...;
174</programlisting>
175   And finally create the transform to connect them all together:
176<programlisting>
177CREATE TRANSFORM FOR hstore LANGUAGE plpythonu (
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>
215