1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
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 15.6 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</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpython-data.html#id-1.8.11.10.3">46.2.1. Data Type Mapping</a></span></dt><dt><span class="sect2"><a href="plpython-data.html#id-1.8.11.10.4">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#id-1.8.11.10.6">46.2.4. Composite Types</a></span></dt><dt><span class="sect2"><a href="plpython-data.html#id-1.8.11.10.7">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="id-1.8.11.10.3"><div class="titlepage"><div><div><h3 class="title">46.2.1. Data Type Mapping</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="id-1.8.11.10.4"><div class="titlepage"><div><div><h3 class="title">46.2.2. Null, None</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</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="id-1.8.11.10.6"><div class="titlepage"><div><div><h3 class="title">46.2.4. Composite Types</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="id-1.8.11.10.7"><div class="titlepage"><div><div><h3 class="title">46.2.5. Set-Returning Functions</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 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 46.3. Sharing Data</td></tr></table></div></body></html>
|
