diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createtransform.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createtransform.html | 106 |
1 files changed, 106 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createtransform.html b/doc/src/sgml/html/sql-createtransform.html new file mode 100644 index 0000000..50623e2 --- /dev/null +++ b/doc/src/sgml/html/sql-createtransform.html @@ -0,0 +1,106 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE TRANSFORM</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createtstemplate.html" title="CREATE TEXT SEARCH TEMPLATE" /><link rel="next" href="sql-createtrigger.html" title="CREATE TRIGGER" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE TRANSFORM</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createtstemplate.html" title="CREATE TEXT SEARCH TEMPLATE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createtrigger.html" title="CREATE TRIGGER">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATETRANSFORM"><div class="titlepage"></div><a id="id-1.9.3.92.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE TRANSFORM</span></h2><p>CREATE TRANSFORM — define a new transform</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +CREATE [ OR REPLACE ] TRANSFORM FOR <em class="replaceable"><code>type_name</code></em> LANGUAGE <em class="replaceable"><code>lang_name</code></em> ( + FROM SQL WITH FUNCTION <em class="replaceable"><code>from_sql_function_name</code></em> [ (<em class="replaceable"><code>argument_type</code></em> [, ...]) ], + TO SQL WITH FUNCTION <em class="replaceable"><code>to_sql_function_name</code></em> [ (<em class="replaceable"><code>argument_type</code></em> [, ...]) ] +); +</pre></div><div class="refsect1" id="SQL-CREATETRANSFORM-DESCRIPTION"><h2>Description</h2><p> + <code class="command">CREATE TRANSFORM</code> defines a new transform. + <code class="command">CREATE OR REPLACE TRANSFORM</code> will either create a new + transform, or replace an existing definition. + </p><p> + A transform specifies how to adapt a data type to a procedural language. + For example, when writing a function in PL/Python using + the <code class="type">hstore</code> type, PL/Python has no prior knowledge how to + present <code class="type">hstore</code> 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. + </p><p> + A transform specifies two functions: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + A <span class="quote">“<span class="quote">from SQL</span>”</span> 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. + </p></li><li class="listitem"><p> + A <span class="quote">“<span class="quote">to SQL</span>”</span> 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. + </p></li></ul></div><p> + 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.) + </p><p> + To be able to create a transform, you must own and + have <code class="literal">USAGE</code> privilege on the type, have + <code class="literal">USAGE</code> privilege on the language, and own and + have <code class="literal">EXECUTE</code> privilege on the from-SQL and to-SQL + functions, if specified. + </p></div><div class="refsect1" id="id-1.9.3.92.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>type_name</code></em></span></dt><dd><p> + The name of the data type of the transform. + </p></dd><dt><span class="term"><em class="replaceable"><code>lang_name</code></em></span></dt><dd><p> + The name of the language of the transform. + </p></dd><dt><span class="term"><code class="literal"><em class="replaceable"><code>from_sql_function_name</code></em>[(<em class="replaceable"><code>argument_type</code></em> [, ...])]</code></span></dt><dd><p> + The name of the function for converting the type from the SQL + environment to the language. It must take one argument of + type <code class="type">internal</code> and return type <code class="type">internal</code>. 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 <code class="type">internal</code> without at + least one argument of type <code class="type">internal</code>.) 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. + </p></dd><dt><span class="term"><code class="literal"><em class="replaceable"><code>to_sql_function_name</code></em>[(<em class="replaceable"><code>argument_type</code></em> [, ...])]</code></span></dt><dd><p> + The name of the function for converting the type from the language to + the SQL environment. It must take one argument of type + <code class="type">internal</code> 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. + </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATETRANSFORM-NOTES"><h2>Notes</h2><p> + Use <a class="link" href="sql-droptransform.html" title="DROP TRANSFORM"><code class="command">DROP TRANSFORM</code></a> to remove transforms. + </p></div><div class="refsect1" id="SQL-CREATETRANSFORM-EXAMPLES"><h2>Examples</h2><p> + To create a transform for type <code class="type">hstore</code> and language + <code class="literal">plpython3u</code>, first set up the type and the language: +</p><pre class="programlisting"> +CREATE TYPE hstore ...; + +CREATE EXTENSION plpython3u; +</pre><p> + Then create the necessary functions: +</p><pre class="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 ...; +</pre><p> + And finally create the transform to connect them all together: +</p><pre class="programlisting"> +CREATE TRANSFORM FOR hstore LANGUAGE plpython3u ( + FROM SQL WITH FUNCTION hstore_to_plpython(internal), + TO SQL WITH FUNCTION plpython_to_hstore(internal) +); +</pre><p> + In practice, these commands would be wrapped up in an extension. + </p><p> + The <code class="filename">contrib</code> section contains a number of extensions + that provide transforms, which can serve as real-world examples. + </p></div><div class="refsect1" id="SQL-CREATETRANSFORM-COMPAT"><h2>Compatibility</h2><p> + This form of <code class="command">CREATE TRANSFORM</code> is a + <span class="productname">PostgreSQL</span> extension. There is a <code class="command">CREATE + TRANSFORM</code> command in the <acronym class="acronym">SQL</acronym> standard, but it + is for adapting data types to client languages. That usage is not supported + by <span class="productname">PostgreSQL</span>. + </p></div><div class="refsect1" id="SQL-CREATETRANSFORM-SEEALSO"><h2>See Also</h2><p> + <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>, + <a class="xref" href="sql-createlanguage.html" title="CREATE LANGUAGE"><span class="refentrytitle">CREATE LANGUAGE</span></a>, + <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a>, + <a class="xref" href="sql-droptransform.html" title="DROP TRANSFORM"><span class="refentrytitle">DROP TRANSFORM</span></a> + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createtstemplate.html" title="CREATE TEXT SEARCH TEMPLATE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createtrigger.html" title="CREATE TRIGGER">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE TEXT SEARCH TEMPLATE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE TRIGGER</td></tr></table></div></body></html>
\ No newline at end of file |