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