diff options
Diffstat (limited to 'doc/src/sgml/html/plpython-data.html')
| -rw-r--r-- | doc/src/sgml/html/plpython-data.html | 343 |
1 files changed, 343 insertions, 0 deletions
diff --git a/doc/src/sgml/html/plpython-data.html b/doc/src/sgml/html/plpython-data.html new file mode 100644 index 0000000..002033c --- /dev/null +++ b/doc/src/sgml/html/plpython-data.html @@ -0,0 +1,343 @@ +<?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>46.2. Data Values</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="plpython-funcs.html" title="46.1. PL/Python Functions" /><link rel="next" href="plpython-sharing.html" title="46.3. Sharing Data" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">46.2. Data Values</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpython-funcs.html" title="46.1. PL/Python Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Up</a></td><th width="60%" align="center">Chapter 46. PL/Python — Python Procedural Language</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="plpython-sharing.html" title="46.3. Sharing Data">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPYTHON-DATA"><div class="titlepage"><div><div><h2 class="title" style="clear: both">46.2. Data Values <a href="#PLPYTHON-DATA" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpython-data.html#PLPYTHON-DATA-TYPE-MAPPING">46.2.1. Data Type Mapping</a></span></dt><dt><span class="sect2"><a href="plpython-data.html#PLPYTHON-DATA-NULL">46.2.2. Null, None</a></span></dt><dt><span class="sect2"><a href="plpython-data.html#PLPYTHON-ARRAYS">46.2.3. Arrays, Lists</a></span></dt><dt><span class="sect2"><a href="plpython-data.html#PLPYTHON-DATA-COMPOSITE-TYPES">46.2.4. Composite Types</a></span></dt><dt><span class="sect2"><a href="plpython-data.html#PLPYTHON-DATA-SET-RETURNING-FUNCS">46.2.5. Set-Returning Functions</a></span></dt></dl></div><p> + Generally speaking, the aim of PL/Python is to provide + a <span class="quote">“<span class="quote">natural</span>”</span> mapping between the PostgreSQL and the + Python worlds. This informs the data mapping rules described + below. + </p><div class="sect2" id="PLPYTHON-DATA-TYPE-MAPPING"><div class="titlepage"><div><div><h3 class="title">46.2.1. Data Type Mapping <a href="#PLPYTHON-DATA-TYPE-MAPPING" class="id_link">#</a></h3></div></div></div><p> + When a PL/Python function is called, its arguments are converted from + their PostgreSQL data type to a corresponding Python type: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + PostgreSQL <code class="type">boolean</code> is converted to Python <code class="type">bool</code>. + </p></li><li class="listitem"><p> + PostgreSQL <code class="type">smallint</code>, <code class="type">int</code>, <code class="type">bigint</code> + and <code class="type">oid</code> are converted to Python <code class="type">int</code>. + </p></li><li class="listitem"><p> + PostgreSQL <code class="type">real</code> and <code class="type">double</code> are converted to + Python <code class="type">float</code>. + </p></li><li class="listitem"><p> + PostgreSQL <code class="type">numeric</code> is converted to + Python <code class="type">Decimal</code>. This type is imported from + the <code class="literal">cdecimal</code> package if that is available. + Otherwise, + <code class="literal">decimal.Decimal</code> from the standard library will be + used. <code class="literal">cdecimal</code> is significantly faster + than <code class="literal">decimal</code>. In Python 3.3 and up, + however, <code class="literal">cdecimal</code> has been integrated into the + standard library under the name <code class="literal">decimal</code>, so there is + no longer any difference. + </p></li><li class="listitem"><p> + PostgreSQL <code class="type">bytea</code> is converted to Python <code class="type">bytes</code>. + </p></li><li class="listitem"><p> + All other data types, including the PostgreSQL character string types, + are converted to a Python <code class="type">str</code> (in Unicode like all Python + strings). + </p></li><li class="listitem"><p> + For nonscalar data types, see below. + </p></li></ul></div><p> + </p><p> + When a PL/Python function returns, its return value is converted to the + function's declared PostgreSQL return data type as follows: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + When the PostgreSQL return type is <code class="type">boolean</code>, the + return value will be evaluated for truth according to the + <span class="emphasis"><em>Python</em></span> rules. That is, 0 and empty string + are false, but notably <code class="literal">'f'</code> is true. + </p></li><li class="listitem"><p> + When the PostgreSQL return type is <code class="type">bytea</code>, the return value + will be converted to Python <code class="type">bytes</code> using the respective + Python built-ins, with the result being converted to + <code class="type">bytea</code>. + </p></li><li class="listitem"><p> + For all other PostgreSQL return types, the return value is converted + to a string using the Python built-in <code class="literal">str</code>, and the + result is passed to the input function of the PostgreSQL data type. + (If the Python value is a <code class="type">float</code>, it is converted using + the <code class="literal">repr</code> built-in instead of <code class="literal">str</code>, to + avoid loss of precision.) + </p><p> + Strings are automatically converted to the PostgreSQL server encoding + when they are passed to PostgreSQL. + </p></li><li class="listitem"><p> + For nonscalar data types, see below. + </p></li></ul></div><p> + + Note that logical mismatches between the declared PostgreSQL + return type and the Python data type of the actual return object + are not flagged; the value will be converted in any case. + </p></div><div class="sect2" id="PLPYTHON-DATA-NULL"><div class="titlepage"><div><div><h3 class="title">46.2.2. Null, None <a href="#PLPYTHON-DATA-NULL" class="id_link">#</a></h3></div></div></div><p> + If an SQL null value<a id="id-1.8.11.10.4.2.1" class="indexterm"></a> is passed to a + function, the argument value will appear as <code class="symbol">None</code> in + Python. For example, the function definition of <code class="function">pymax</code> + shown in <a class="xref" href="plpython-funcs.html" title="46.1. PL/Python Functions">Section 46.1</a> will return the wrong answer for null + inputs. We could add <code class="literal">STRICT</code> to the function definition + to make <span class="productname">PostgreSQL</span> do something more reasonable: + if a null value is passed, the function will not be called at all, + but will just return a null result automatically. Alternatively, + we could check for null inputs in the function body: + +</p><pre class="programlisting"> +CREATE FUNCTION pymax (a integer, b integer) + RETURNS integer +AS $$ + if (a is None) or (b is None): + return None + if a > b: + return a + return b +$$ LANGUAGE plpython3u; +</pre><p> + + As shown above, to return an SQL null value from a PL/Python + function, return the value <code class="symbol">None</code>. This can be done whether the + function is strict or not. + </p></div><div class="sect2" id="PLPYTHON-ARRAYS"><div class="titlepage"><div><div><h3 class="title">46.2.3. Arrays, Lists <a href="#PLPYTHON-ARRAYS" class="id_link">#</a></h3></div></div></div><p> + SQL array values are passed into PL/Python as a Python list. To + return an SQL array value out of a PL/Python function, return a + Python list: + +</p><pre class="programlisting"> +CREATE FUNCTION return_arr() + RETURNS int[] +AS $$ +return [1, 2, 3, 4, 5] +$$ LANGUAGE plpython3u; + +SELECT return_arr(); + return_arr +------------- + {1,2,3,4,5} +(1 row) +</pre><p> + + Multidimensional arrays are passed into PL/Python as nested Python lists. + A 2-dimensional array is a list of lists, for example. When returning + a multi-dimensional SQL array out of a PL/Python function, the inner + lists at each level must all be of the same size. For example: + +</p><pre class="programlisting"> +CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$ +plpy.info(x, type(x)) +return x +$$ LANGUAGE plpython3u; + +SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]); +INFO: ([[1, 2, 3], [4, 5, 6]], <type 'list'>) + test_type_conversion_array_int4 +--------------------------------- + {{1,2,3},{4,5,6}} +(1 row) +</pre><p> + + Other Python sequences, like tuples, are also accepted for + backwards-compatibility with PostgreSQL versions 9.6 and below, when + multi-dimensional arrays were not supported. However, they are always + treated as one-dimensional arrays, because they are ambiguous with + composite types. For the same reason, when a composite type is used in a + multi-dimensional array, it must be represented by a tuple, rather than a + list. + </p><p> + Note that in Python, strings are sequences, which can have + undesirable effects that might be familiar to Python programmers: + +</p><pre class="programlisting"> +CREATE FUNCTION return_str_arr() + RETURNS varchar[] +AS $$ +return "hello" +$$ LANGUAGE plpython3u; + +SELECT return_str_arr(); + return_str_arr +---------------- + {h,e,l,l,o} +(1 row) +</pre><p> + </p></div><div class="sect2" id="PLPYTHON-DATA-COMPOSITE-TYPES"><div class="titlepage"><div><div><h3 class="title">46.2.4. Composite Types <a href="#PLPYTHON-DATA-COMPOSITE-TYPES" class="id_link">#</a></h3></div></div></div><p> + Composite-type arguments are passed to the function as Python mappings. The + element names of the mapping are the attribute names of the composite type. + If an attribute in the passed row has the null value, it has the value + <code class="symbol">None</code> in the mapping. Here is an example: + +</p><pre class="programlisting"> +CREATE TABLE employee ( + name text, + salary integer, + age integer +); + +CREATE FUNCTION overpaid (e employee) + RETURNS boolean +AS $$ + if e["salary"] > 200000: + return True + if (e["age"] < 30) and (e["salary"] > 100000): + return True + return False +$$ LANGUAGE plpython3u; +</pre><p> + </p><p> + There are multiple ways to return row or composite types from a Python + function. The following examples assume we have: + +</p><pre class="programlisting"> +CREATE TYPE named_value AS ( + name text, + value integer +); +</pre><p> + + A composite result can be returned as a: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Sequence type (a tuple or list, but not a set because + it is not indexable)</span></dt><dd><p> + Returned sequence objects must have the same number of items as the + composite result type has fields. The item with index 0 is assigned to + the first field of the composite type, 1 to the second and so on. For + example: + +</p><pre class="programlisting"> +CREATE FUNCTION make_pair (name text, value integer) + RETURNS named_value +AS $$ + return ( name, value ) + # or alternatively, as list: return [ name, value ] +$$ LANGUAGE plpython3u; +</pre><p> + + To return an SQL null for any column, insert <code class="symbol">None</code> at + the corresponding position. + </p><p> + When an array of composite types is returned, it cannot be returned as a list, + because it is ambiguous whether the Python list represents a composite type, + or another array dimension. + </p></dd><dt><span class="term">Mapping (dictionary)</span></dt><dd><p> + The value for each result type column is retrieved from the mapping + with the column name as key. Example: + +</p><pre class="programlisting"> +CREATE FUNCTION make_pair (name text, value integer) + RETURNS named_value +AS $$ + return { "name": name, "value": value } +$$ LANGUAGE plpython3u; +</pre><p> + + Any extra dictionary key/value pairs are ignored. Missing keys are + treated as errors. + To return an SQL null value for any column, insert + <code class="symbol">None</code> with the corresponding column name as the key. + </p></dd><dt><span class="term">Object (any object providing method <code class="literal">__getattr__</code>)</span></dt><dd><p> + This works the same as a mapping. + Example: + +</p><pre class="programlisting"> +CREATE FUNCTION make_pair (name text, value integer) + RETURNS named_value +AS $$ + class named_value: + def __init__ (self, n, v): + self.name = n + self.value = v + return named_value(name, value) + + # or simply + class nv: pass + nv.name = name + nv.value = value + return nv +$$ LANGUAGE plpython3u; +</pre><p> + </p></dd></dl></div><p> + </p><p> + Functions with <code class="literal">OUT</code> parameters are also supported. For example: +</p><pre class="programlisting"> +CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$ +return (1, 2) +$$ LANGUAGE plpython3u; + +SELECT * FROM multiout_simple(); +</pre><p> + </p><p> + Output parameters of procedures are passed back the same way. For example: +</p><pre class="programlisting"> +CREATE PROCEDURE python_triple(INOUT a integer, INOUT b integer) AS $$ +return (a * 3, b * 3) +$$ LANGUAGE plpython3u; + +CALL python_triple(5, 10); +</pre><p> + </p></div><div class="sect2" id="PLPYTHON-DATA-SET-RETURNING-FUNCS"><div class="titlepage"><div><div><h3 class="title">46.2.5. Set-Returning Functions <a href="#PLPYTHON-DATA-SET-RETURNING-FUNCS" class="id_link">#</a></h3></div></div></div><p> + A <span class="application">PL/Python</span> function can also return sets of + scalar or composite types. There are several ways to achieve this because + the returned object is internally turned into an iterator. The following + examples assume we have composite type: + +</p><pre class="programlisting"> +CREATE TYPE greeting AS ( + how text, + who text +); +</pre><p> + + A set result can be returned from a: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Sequence type (tuple, list, set)</span></dt><dd><p> +</p><pre class="programlisting"> +CREATE FUNCTION greet (how text) + RETURNS SETOF greeting +AS $$ + # return tuple containing lists as composite types + # all other combinations work also + return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] ) +$$ LANGUAGE plpython3u; +</pre><p> + </p></dd><dt><span class="term">Iterator (any object providing <code class="symbol">__iter__</code> and + <code class="symbol">next</code> methods)</span></dt><dd><p> +</p><pre class="programlisting"> +CREATE FUNCTION greet (how text) + RETURNS SETOF greeting +AS $$ + class producer: + def __init__ (self, how, who): + self.how = how + self.who = who + self.ndx = -1 + + def __iter__ (self): + return self + + def next (self): + self.ndx += 1 + if self.ndx == len(self.who): + raise StopIteration + return ( self.how, self.who[self.ndx] ) + + return producer(how, [ "World", "PostgreSQL", "PL/Python" ]) +$$ LANGUAGE plpython3u; +</pre><p> + </p></dd><dt><span class="term">Generator (<code class="literal">yield</code>)</span></dt><dd><p> +</p><pre class="programlisting"> +CREATE FUNCTION greet (how text) + RETURNS SETOF greeting +AS $$ + for who in [ "World", "PostgreSQL", "PL/Python" ]: + yield ( how, who ) +$$ LANGUAGE plpython3u; +</pre><p> + + </p></dd></dl></div><p> + </p><p> + Set-returning functions with <code class="literal">OUT</code> parameters + (using <code class="literal">RETURNS SETOF record</code>) are also + supported. For example: +</p><pre class="programlisting"> +CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer) RETURNS SETOF record AS $$ +return [(1, 2)] * n +$$ LANGUAGE plpython3u; + +SELECT * FROM multiout_simple_setof(3); +</pre><p> + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpython-funcs.html" title="46.1. PL/Python Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpython.html" title="Chapter 46. PL/Python — Python Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpython-sharing.html" title="46.3. Sharing Data">Next</a></td></tr><tr><td width="40%" align="left" valign="top">46.1. PL/Python Functions </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"> 46.3. Sharing Data</td></tr></table></div></body></html>
\ No newline at end of file |