diff options
Diffstat (limited to 'doc/src/sgml/ref/create_transform.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_transform.sgml | 214 |
1 files changed, 214 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_transform.sgml b/doc/src/sgml/ref/create_transform.sgml new file mode 100644 index 0000000..34bdc60 --- /dev/null +++ b/doc/src/sgml/ref/create_transform.sgml @@ -0,0 +1,214 @@ +<!-- +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> |