<!--
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>