codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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>3 Generally speaking, the aim of PL/Python is to provide4 a <span class="quote">“<span class="quote">natural</span>”</span> mapping between the PostgreSQL and the5 Python worlds. This informs the data mapping rules described6 below.7 </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>8 When a PL/Python function is called, its arguments are converted from9 their PostgreSQL data type to a corresponding Python type:10 11 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>12 PostgreSQL <code class="type">boolean</code> is converted to Python <code class="type">bool</code>.13 </p></li><li class="listitem"><p>14 PostgreSQL <code class="type">smallint</code>, <code class="type">int</code>, <code class="type">bigint</code>15 and <code class="type">oid</code> are converted to Python <code class="type">int</code>.16 </p></li><li class="listitem"><p>17 PostgreSQL <code class="type">real</code> and <code class="type">double</code> are converted to18 Python <code class="type">float</code>.19 </p></li><li class="listitem"><p>20 PostgreSQL <code class="type">numeric</code> is converted to21 Python <code class="type">Decimal</code>. This type is imported from22 the <code class="literal">cdecimal</code> package if that is available.23 Otherwise,24 <code class="literal">decimal.Decimal</code> from the standard library will be25 used. <code class="literal">cdecimal</code> is significantly faster26 than <code class="literal">decimal</code>. In Python 3.3 and up,27 however, <code class="literal">cdecimal</code> has been integrated into the28 standard library under the name <code class="literal">decimal</code>, so there is29 no longer any difference.30 </p></li><li class="listitem"><p>31 PostgreSQL <code class="type">bytea</code> is converted to Python <code class="type">bytes</code>.32 </p></li><li class="listitem"><p>33 All other data types, including the PostgreSQL character string types,34 are converted to a Python <code class="type">str</code> (in Unicode like all Python35 strings).36 </p></li><li class="listitem"><p>37 For nonscalar data types, see below.38 </p></li></ul></div><p>39 </p><p>40 When a PL/Python function returns, its return value is converted to the41 function's declared PostgreSQL return data type as follows:42 43 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>44 When the PostgreSQL return type is <code class="type">boolean</code>, the45 return value will be evaluated for truth according to the46 <span class="emphasis"><em>Python</em></span> rules. That is, 0 and empty string47 are false, but notably <code class="literal">'f'</code> is true.48 </p></li><li class="listitem"><p>49 When the PostgreSQL return type is <code class="type">bytea</code>, the return value50 will be converted to Python <code class="type">bytes</code> using the respective51 Python built-ins, with the result being converted to52 <code class="type">bytea</code>.53 </p></li><li class="listitem"><p>54 For all other PostgreSQL return types, the return value is converted55 to a string using the Python built-in <code class="literal">str</code>, and the56 result is passed to the input function of the PostgreSQL data type.57 (If the Python value is a <code class="type">float</code>, it is converted using58 the <code class="literal">repr</code> built-in instead of <code class="literal">str</code>, to59 avoid loss of precision.)60 </p><p>61 Strings are automatically converted to the PostgreSQL server encoding62 when they are passed to PostgreSQL.63 </p></li><li class="listitem"><p>64 For nonscalar data types, see below.65 </p></li></ul></div><p>66 67 Note that logical mismatches between the declared PostgreSQL68 return type and the Python data type of the actual return object69 are not flagged; the value will be converted in any case.70 </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>71 If an SQL null value<a id="id-1.8.11.10.4.2.1" class="indexterm"></a> is passed to a72 function, the argument value will appear as <code class="symbol">None</code> in73 Python. For example, the function definition of <code class="function">pymax</code>74 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 null75 inputs. We could add <code class="literal">STRICT</code> to the function definition76 to make <span class="productname">PostgreSQL</span> do something more reasonable:77 if a null value is passed, the function will not be called at all,78 but will just return a null result automatically. Alternatively,79 we could check for null inputs in the function body:80 81</p><pre class="programlisting">82CREATE FUNCTION pymax (a integer, b integer)83 RETURNS integer84AS $$85 if (a is None) or (b is None):86 return None87 if a > b:88 return a89 return b90$$ LANGUAGE plpython3u;91</pre><p>92 93 As shown above, to return an SQL null value from a PL/Python94 function, return the value <code class="symbol">None</code>. This can be done whether the95 function is strict or not.96 </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>97 SQL array values are passed into PL/Python as a Python list. To98 return an SQL array value out of a PL/Python function, return a99 Python list:100 101</p><pre class="programlisting">102CREATE FUNCTION return_arr()103 RETURNS int[]104AS $$105return [1, 2, 3, 4, 5]106$$ LANGUAGE plpython3u;107 108SELECT return_arr();109 return_arr110-------------111 {1,2,3,4,5}112(1 row)113</pre><p>114 115 Multidimensional arrays are passed into PL/Python as nested Python lists.116 A 2-dimensional array is a list of lists, for example. When returning117 a multi-dimensional SQL array out of a PL/Python function, the inner118 lists at each level must all be of the same size. For example:119 120</p><pre class="programlisting">121CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$122plpy.info(x, type(x))123return x124$$ LANGUAGE plpython3u;125 126SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);127INFO: ([[1, 2, 3], [4, 5, 6]], <type 'list'>)128 test_type_conversion_array_int4129---------------------------------130 {{1,2,3},{4,5,6}}131(1 row)132</pre><p>133 134 Other Python sequences, like tuples, are also accepted for135 backwards-compatibility with PostgreSQL versions 9.6 and below, when136 multi-dimensional arrays were not supported. However, they are always137 treated as one-dimensional arrays, because they are ambiguous with138 composite types. For the same reason, when a composite type is used in a139 multi-dimensional array, it must be represented by a tuple, rather than a140 list.141 </p><p>142 Note that in Python, strings are sequences, which can have143 undesirable effects that might be familiar to Python programmers:144 145</p><pre class="programlisting">146CREATE FUNCTION return_str_arr()147 RETURNS varchar[]148AS $$149return "hello"150$$ LANGUAGE plpython3u;151 152SELECT return_str_arr();153 return_str_arr154----------------155 {h,e,l,l,o}156(1 row)157</pre><p>158 </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>159 Composite-type arguments are passed to the function as Python mappings. The160 element names of the mapping are the attribute names of the composite type.161 If an attribute in the passed row has the null value, it has the value162 <code class="symbol">None</code> in the mapping. Here is an example:163 164</p><pre class="programlisting">165CREATE TABLE employee (166 name text,167 salary integer,168 age integer169);170 171CREATE FUNCTION overpaid (e employee)172 RETURNS boolean173AS $$174 if e["salary"] > 200000:175 return True176 if (e["age"] < 30) and (e["salary"] > 100000):177 return True178 return False179$$ LANGUAGE plpython3u;180</pre><p>181 </p><p>182 There are multiple ways to return row or composite types from a Python183 function. The following examples assume we have:184 185</p><pre class="programlisting">186CREATE TYPE named_value AS (187 name text,188 value integer189);190</pre><p>191 192 A composite result can be returned as a:193 194 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Sequence type (a tuple or list, but not a set because195 it is not indexable)</span></dt><dd><p>196 Returned sequence objects must have the same number of items as the197 composite result type has fields. The item with index 0 is assigned to198 the first field of the composite type, 1 to the second and so on. For199 example:200 201</p><pre class="programlisting">202CREATE FUNCTION make_pair (name text, value integer)203 RETURNS named_value204AS $$205 return ( name, value )206 # or alternatively, as list: return [ name, value ]207$$ LANGUAGE plpython3u;208</pre><p>209 210 To return an SQL null for any column, insert <code class="symbol">None</code> at211 the corresponding position.212 </p><p>213 When an array of composite types is returned, it cannot be returned as a list,214 because it is ambiguous whether the Python list represents a composite type,215 or another array dimension.216 </p></dd><dt><span class="term">Mapping (dictionary)</span></dt><dd><p>217 The value for each result type column is retrieved from the mapping218 with the column name as key. Example:219 220</p><pre class="programlisting">221CREATE FUNCTION make_pair (name text, value integer)222 RETURNS named_value223AS $$224 return { "name": name, "value": value }225$$ LANGUAGE plpython3u;226</pre><p>227 228 Any extra dictionary key/value pairs are ignored. Missing keys are229 treated as errors.230 To return an SQL null value for any column, insert231 <code class="symbol">None</code> with the corresponding column name as the key.232 </p></dd><dt><span class="term">Object (any object providing method <code class="literal">__getattr__</code>)</span></dt><dd><p>233 This works the same as a mapping.234 Example:235 236</p><pre class="programlisting">237CREATE FUNCTION make_pair (name text, value integer)238 RETURNS named_value239AS $$240 class named_value:241 def __init__ (self, n, v):242 self.name = n243 self.value = v244 return named_value(name, value)245 246 # or simply247 class nv: pass248 nv.name = name249 nv.value = value250 return nv251$$ LANGUAGE plpython3u;252</pre><p>253 </p></dd></dl></div><p>254 </p><p>255 Functions with <code class="literal">OUT</code> parameters are also supported. For example:256</p><pre class="programlisting">257CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$258return (1, 2)259$$ LANGUAGE plpython3u;260 261SELECT * FROM multiout_simple();262</pre><p>263 </p><p>264 Output parameters of procedures are passed back the same way. For example:265</p><pre class="programlisting">266CREATE PROCEDURE python_triple(INOUT a integer, INOUT b integer) AS $$267return (a * 3, b * 3)268$$ LANGUAGE plpython3u;269 270CALL python_triple(5, 10);271</pre><p>272 </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>273 A <span class="application">PL/Python</span> function can also return sets of274 scalar or composite types. There are several ways to achieve this because275 the returned object is internally turned into an iterator. The following276 examples assume we have composite type:277 278</p><pre class="programlisting">279CREATE TYPE greeting AS (280 how text,281 who text282);283</pre><p>284 285 A set result can be returned from a:286 287 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">Sequence type (tuple, list, set)</span></dt><dd><p>288</p><pre class="programlisting">289CREATE FUNCTION greet (how text)290 RETURNS SETOF greeting291AS $$292 # return tuple containing lists as composite types293 # all other combinations work also294 return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )295$$ LANGUAGE plpython3u;296</pre><p>297 </p></dd><dt><span class="term">Iterator (any object providing <code class="symbol">__iter__</code> and298 <code class="symbol">next</code> methods)</span></dt><dd><p>299</p><pre class="programlisting">300CREATE FUNCTION greet (how text)301 RETURNS SETOF greeting302AS $$303 class producer:304 def __init__ (self, how, who):305 self.how = how306 self.who = who307 self.ndx = -1308 309 def __iter__ (self):310 return self311 312 def next (self):313 self.ndx += 1314 if self.ndx == len(self.who):315 raise StopIteration316 return ( self.how, self.who[self.ndx] )317 318 return producer(how, [ "World", "PostgreSQL", "PL/Python" ])319$$ LANGUAGE plpython3u;320</pre><p>321 </p></dd><dt><span class="term">Generator (<code class="literal">yield</code>)</span></dt><dd><p>322</p><pre class="programlisting">323CREATE FUNCTION greet (how text)324 RETURNS SETOF greeting325AS $$326 for who in [ "World", "PostgreSQL", "PL/Python" ]:327 yield ( how, who )328$$ LANGUAGE plpython3u;329</pre><p>330 331 </p></dd></dl></div><p>332 </p><p>333 Set-returning functions with <code class="literal">OUT</code> parameters334 (using <code class="literal">RETURNS SETOF record</code>) are also335 supported. For example:336</p><pre class="programlisting">337CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer) RETURNS SETOF record AS $$338return [(1, 2)] * n339$$ LANGUAGE plpython3u;340 341SELECT * FROM multiout_simple_setof(3);342</pre><p>343 </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 46.3. Sharing Data</td></tr></table></div></body></html>