<!--
doc/src/sgml/ref/create_transform.sgml
PostgreSQL documentation
-->

<refentry id="sql-createtransform">
 <indexterm zone="sql-createtransform">
  <primary>CREATE TRANSFORM</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE TRANSFORM</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE TRANSFORM</refname>
  <refpurpose>define a new transform</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE [ OR REPLACE ] TRANSFORM FOR <replaceable>type_name</replaceable> LANGUAGE <replaceable>lang_name</replaceable> (
    FROM SQL WITH FUNCTION <replaceable>from_sql_function_name</replaceable> [ (<replaceable>argument_type</replaceable> [, ...]) ],
    TO SQL WITH FUNCTION <replaceable>to_sql_function_name</replaceable> [ (<replaceable>argument_type</replaceable> [, ...]) ]
);
</synopsis>
 </refsynopsisdiv>

 <refsect1 id="sql-createtransform-description">
  <title>Description</title>

  <para>
   <command>CREATE TRANSFORM</command> defines a new transform.
   <command>CREATE OR REPLACE TRANSFORM</command> will either create a new
   transform, or replace an existing definition.
  </para>

  <para>
   A transform specifies how to adapt a data type to a procedural language.
   For example, when writing a function in PL/Python using
   the <type>hstore</type> type, PL/Python has no prior knowledge how to
   present <type>hstore</type> 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.
  </para>

  <para>
   A transform specifies two functions:
   <itemizedlist>
    <listitem>
     <para>
      A <quote>from SQL</quote> 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.
     </para>
    </listitem>

    <listitem>
     <para>
      A <quote>to SQL</quote> 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.
     </para>
    </listitem>
   </itemizedlist>
   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.)
  </para>

  <para>
   To be able to create a transform, you must own and
   have <literal>USAGE</literal> privilege on the type, have
   <literal>USAGE</literal> privilege on the language, and own and
   have <literal>EXECUTE</literal> privilege on the from-SQL and to-SQL
   functions, if specified.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

   <variablelist>
    <varlistentry>
     <term><replaceable>type_name</replaceable></term>

     <listitem>
      <para>
       The name of the data type of the transform.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable>lang_name</replaceable></term>

     <listitem>
      <para>
       The name of the language of the transform.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal><replaceable>from_sql_function_name</replaceable>[(<replaceable>argument_type</replaceable> [, ...])]</literal></term>

     <listitem>
      <para>
       The name of the function for converting the type from the SQL
       environment to the language.  It must take one argument of
       type <type>internal</type> and return type <type>internal</type>.  The
       actual argument will be of the type for the transform, and the function
       should be coded as if it were.  (But it is not allowed to declare an
       SQL-level function returning <type>internal</type> without at
       least one argument of type <type>internal</type>.)  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.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal><replaceable>to_sql_function_name</replaceable>[(<replaceable>argument_type</replaceable> [, ...])]</literal></term>

     <listitem>
      <para>
       The name of the function for converting the type from the language to
       the SQL environment.  It must take one argument of type
       <type>internal</type> 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.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
 </refsect1>

 <refsect1 id="sql-createtransform-notes">
  <title>Notes</title>

  <para>
   Use <link linkend="sql-droptransform"><command>DROP TRANSFORM</command></link> to remove transforms.
  </para>
 </refsect1>

 <refsect1 id="sql-createtransform-examples">
  <title>Examples</title>

  <para>
   To create a transform for type <type>hstore</type> and language
   <literal>plpython3u</literal>, first set up the type and the language:
<programlisting>
CREATE TYPE hstore ...;

CREATE EXTENSION plpython3u;
</programlisting>
   Then create the necessary functions:
<programlisting>
CREATE FUNCTION hstore_to_plpython(val internal) RETURNS internal
LANGUAGE C STRICT IMMUTABLE
AS ...;

CREATE FUNCTION plpython_to_hstore(val internal) RETURNS hstore
LANGUAGE C STRICT IMMUTABLE
AS ...;
</programlisting>
   And finally create the transform to connect them all together:
<programlisting>
CREATE TRANSFORM FOR hstore LANGUAGE plpython3u (
    FROM SQL WITH FUNCTION hstore_to_plpython(internal),
    TO SQL WITH FUNCTION plpython_to_hstore(internal)
);
</programlisting>
   In practice, these commands would be wrapped up in an extension.
  </para>

  <para>
   The <filename>contrib</filename> section contains a number of extensions
   that provide transforms, which can serve as real-world examples.
  </para>
 </refsect1>

 <refsect1 id="sql-createtransform-compat">
  <title>Compatibility</title>

  <para>
   This form of <command>CREATE TRANSFORM</command> is a
   <productname>PostgreSQL</productname> extension.  There is a <command>CREATE
   TRANSFORM</command> command in the <acronym>SQL</acronym> standard, but it
   is for adapting data types to client languages.  That usage is not supported
   by <productname>PostgreSQL</productname>.
  </para>
 </refsect1>

 <refsect1 id="sql-createtransform-seealso">
  <title>See Also</title>

  <para>
   <xref linkend="sql-createfunction"/>,
   <xref linkend="sql-createlanguage"/>,
   <xref linkend="sql-createtype"/>,
   <xref linkend="sql-droptransform"/>
  </para>
 </refsect1>

</refentry>