3
<chapter id="plpython">
4
<title>PL/Python - Python Procedural Language</title>
6
<indexterm zone="plpython"><primary>PL/Python</></>
7
<indexterm zone="plpython"><primary>Python</></>
10
The <application>PL/Python</application> procedural language allows
11
<productname>PostgreSQL</productname> functions to be written in the
12
<ulink url="http://www.python.org">Python language</ulink>.
16
To install PL/Python in a particular database, use
17
<literal>createlang plpythonu <replaceable>dbname</></literal>.
22
If a language is installed into <literal>template1</>, all subsequently
23
created databases will have the language installed automatically.
28
As of <productname>PostgreSQL</productname> 7.4, PL/Python is only
29
available as an <quote>untrusted</> language (meaning it does not
30
offer any way of restricting what users can do in it). It has
31
therefore been renamed to <literal>plpythonu</>. The trusted
32
variant <literal>plpython</> might become available again in future,
33
if a new secure execution mechanism is developed in Python.
38
Users of source packages must specially enable the build of
39
PL/Python during the installation process. (Refer to the
40
installation instructions for more information.) Users of binary
41
packages might find PL/Python in a separate subpackage.
45
<sect1 id="plpython-funcs">
46
<title>PL/Python Functions</title>
49
Functions in PL/Python are declared via the standard <xref
50
linkend="sql-createfunction" endterm="sql-createfunction-title">
54
CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-list</replaceable>)
55
RETURNS <replaceable>return-type</replaceable>
57
# PL/Python function body
58
$$ LANGUAGE plpythonu;
63
The body of a function is simply a Python script. When the function
64
is called, its arguments are passed as elements of the array
65
<varname>args[]</varname>; named arguments are also passed as ordinary
66
variables to the Python script. The result is returned from the Python code
67
in the usual way, with <literal>return</literal> or
68
<literal>yield</literal> (in case of a result-set statement).
72
For example, a function to return the greater of two integers can be
76
CREATE FUNCTION pymax (a integer, b integer)
82
$$ LANGUAGE plpythonu;
85
The Python code that is given as the body of the function definition
86
is transformed into a Python function. For example, the above results in:
89
def __plpython_procedure_pymax_23456():
95
assuming that 23456 is the OID assigned to the function by
96
<productname>PostgreSQL</productname>.
100
The <productname>PostgreSQL</> function parameters are available in
101
the global <varname>args</varname> list. In the
102
<function>pymax</function> example, <varname>args[0]</varname> contains
103
whatever was passed in as the first argument and
104
<varname>args[1]</varname> contains the second argument's
105
value. Alternatively, one can use named parameters as shown in the example
106
above. Use of named parameters is usually more readable.
110
If an SQL null value<indexterm><primary>null value</primary><secondary
111
sortas="PL/Python">PL/Python</secondary></indexterm> is passed to a
112
function, the argument value will appear as <symbol>None</symbol> in
113
Python. The above function definition will return the wrong answer for null
114
inputs. We could add <literal>STRICT</literal> to the function definition
115
to make <productname>PostgreSQL</productname> do something more reasonable:
116
if a null value is passed, the function will not be called at all,
117
but will just return a null result automatically. Alternatively,
118
we could check for null inputs in the function body:
121
CREATE FUNCTION pymax (a integer, b integer)
124
if (a is None) or (b is None):
129
$$ LANGUAGE plpythonu;
132
As shown above, to return an SQL null value from a PL/Python
133
function, return the value <symbol>None</symbol>. This can be done whether the
134
function is strict or not.
138
Composite-type arguments are passed to the function as Python mappings. The
139
element names of the mapping are the attribute names of the composite type.
140
If an attribute in the passed row has the null value, it has the value
141
<symbol>None</symbol> in the mapping. Here is an example:
144
CREATE TABLE employee (
150
CREATE FUNCTION overpaid (e employee)
153
if e["salary"] > 200000:
155
if (e["age"] < 30) and (e["salary"] > 100000):
158
$$ LANGUAGE plpythonu;
163
There are multiple ways to return row or composite types from a Python
164
function. The following examples assume we have:
167
CREATE TYPE named_value AS (
173
A composite result can be returned as a:
177
<term>Sequence type (a tuple or list, but not a set because
178
it is not indexable)</term>
181
Returned sequence objects must have the same number of items as the
182
composite result type has fields. The item with index 0 is assigned to
183
the first field of the composite type, 1 to the second and so on. For
187
CREATE FUNCTION make_pair (name text, value integer)
190
return [ name, value ]
191
# or alternatively, as tuple: return ( name, value )
192
$$ LANGUAGE plpythonu;
195
To return a SQL null for any column, insert <symbol>None</symbol> at
196
the corresponding position.
202
<term>Mapping (dictionary)</term>
205
The value for each result type column is retrieved from the mapping
206
with the column name as key. Example:
209
CREATE FUNCTION make_pair (name text, value integer)
212
return { "name": name, "value": value }
213
$$ LANGUAGE plpythonu;
216
Any extra dictionary key/value pairs are ignored. Missing keys are
218
To return a SQL null value for any column, insert
219
<symbol>None</symbol> with the corresponding column name as the key.
225
<term>Object (any object providing method <literal>__getattr__</literal>)</term>
228
This works the same as a mapping.
232
CREATE FUNCTION make_pair (name text, value integer)
236
def __init__ (self, n, v):
239
return named_value(name, value)
246
$$ LANGUAGE plpythonu;
255
If you do not provide a return value, Python returns the default
256
<symbol>None</symbol>. <application>PL/Python</application> translates
257
Python's <symbol>None</symbol> into the SQL null value.
261
A <application>PL/Python</application> function can also return sets of
262
scalar or composite types. There are several ways to achieve this because
263
the returned object is internally turned into an iterator. The following
264
examples assume we have composite type:
267
CREATE TYPE greeting AS (
273
A set result can be returned from a:
277
<term>Sequence type (tuple, list, set)</term>
281
CREATE FUNCTION greet (how text)
282
RETURNS SETOF greeting
284
# return tuple containing lists as composite types
285
# all other combinations work also
286
return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
287
$$ LANGUAGE plpythonu;
294
<term>Iterator (any object providing <symbol>__iter__</symbol> and
295
<symbol>next</symbol> methods)</term>
299
CREATE FUNCTION greet (how text)
300
RETURNS SETOF greeting
303
def __init__ (self, how, who):
313
if self.ndx == len(self.who):
315
return ( self.how, self.who[self.ndx] )
317
return producer(how, [ "World", "PostgreSQL", "PL/Python" ])
318
$$ LANGUAGE plpythonu;
325
<term>Generator (<literal>yield</literal>)</term>
329
CREATE FUNCTION greet (how text)
330
RETURNS SETOF greeting
332
for who in [ "World", "PostgreSQL", "PL/Python" ]:
334
$$ LANGUAGE plpythonu;
339
Currently, due to Python
340
<ulink url="http://sourceforge.net/tracker/index.php?func=detail&aid=1483133&group_id=5470&atid=105470">bug #1483133</ulink>,
341
some debug versions of Python 2.4
342
(configured and compiled with option <literal>--with-pydebug</literal>)
343
are known to crash the <productname>PostgreSQL</productname> server
344
when using an iterator to return a set result.
345
Unpatched versions of Fedora 4 contain this bug.
346
It does not happen in production versions of Python or on patched
347
versions of Fedora 4.
357
The global dictionary <varname>SD</varname> is available to store
358
data between function calls. This variable is private static data.
359
The global dictionary <varname>GD</varname> is public data,
360
available to all Python functions within a session. Use with
361
care.<indexterm><primary>global data</><secondary>in
362
PL/Python</></indexterm>
366
Each function gets its own execution environment in the
367
Python interpreter, so that global data and function arguments from
368
<function>myfunc</function> are not available to
369
<function>myfunc2</function>. The exception is the data in the
370
<varname>GD</varname> dictionary, as mentioned above.
374
<sect1 id="plpython-trigger">
375
<title>Trigger Functions</title>
377
<indexterm zone="plpython-trigger">
378
<primary>trigger</primary>
379
<secondary>in PL/Python</secondary>
383
When a function is used as a trigger, the dictionary
384
<literal>TD</literal> contains trigger-related values.
385
<literal>TD["event"]</> contains
386
the event as a string (<literal>INSERT</>, <literal>UPDATE</>,
387
<literal>DELETE</>, <literal>TRUNCATE</>, or <literal>UNKNOWN</>).
388
<literal>TD["when"]</> contains one of <literal>BEFORE</>,
389
<literal>AFTER</>, or <literal>UNKNOWN</>.
390
<literal>TD["level"]</> contains one of <literal>ROW</>,
391
<literal>STATEMENT</>, or <literal>UNKNOWN</>.
392
For a row-level trigger, the trigger
393
rows are in <literal>TD["new"]</> and/or <literal>TD["old"]</>
394
depending on the trigger event.
395
<literal>TD["name"]</> contains the trigger name,
396
<literal>TD["table_name"]</> contains the name of the table on which the trigger occurred,
397
<literal>TD["table_schema"]</> contains the schema of the table on which the trigger occurred,
398
and <literal>TD["relid"]</> contains the OID of the table on
399
which the trigger occurred. If the <command>CREATE TRIGGER</> command
400
included arguments, they are available in <literal>TD["args"][0]</> to
401
<literal>TD["args"][<replaceable>n</>-1]</>.
405
If <literal>TD["when"]</literal> is <literal>BEFORE</> and
406
<literal>TD["level"]</literal> is <literal>ROW</>, you can
407
return <literal>None</literal> or <literal>"OK"</literal> from the
408
Python function to indicate the row is unmodified,
409
<literal>"SKIP"</> to abort the event, or <literal>"MODIFY"</> to
410
indicate you've modified the row.
411
Otherwise the return value is ignored.
415
<sect1 id="plpython-database">
416
<title>Database Access</title>
419
The PL/Python language module automatically imports a Python module
420
called <literal>plpy</literal>. The functions and constants in
421
this module are available to you in the Python code as
422
<literal>plpy.<replaceable>foo</replaceable></literal>. At present
423
<literal>plpy</literal> implements the functions
424
<literal>plpy.debug(<replaceable>msg</>)</literal>,
425
<literal>plpy.log(<replaceable>msg</>)</literal>,
426
<literal>plpy.info(<replaceable>msg</>)</literal>,
427
<literal>plpy.notice(<replaceable>msg</>)</literal>,
428
<literal>plpy.warning(<replaceable>msg</>)</literal>,
429
<literal>plpy.error(<replaceable>msg</>)</literal>, and
430
<literal>plpy.fatal(<replaceable>msg</>)</literal>.<indexterm><primary>elog</><secondary>in PL/Python</></indexterm>
431
<function>plpy.error</function> and
432
<function>plpy.fatal</function> actually raise a Python exception
433
which, if uncaught, propagates out to the calling query, causing
434
the current transaction or subtransaction to be aborted.
435
<literal>raise plpy.ERROR(<replaceable>msg</>)</literal> and
436
<literal>raise plpy.FATAL(<replaceable>msg</>)</literal> are
437
equivalent to calling
438
<function>plpy.error</function> and
439
<function>plpy.fatal</function>, respectively.
440
The other functions only generate messages of different
442
Whether messages of a particular priority are reported to the client,
443
written to the server log, or both is controlled by the
444
<xref linkend="guc-log-min-messages"> and
445
<xref linkend="guc-client-min-messages"> configuration
446
variables. See <xref linkend="runtime-config"> for more information.
450
Additionally, the <literal>plpy</literal> module provides two
451
functions called <function>execute</function> and
452
<function>prepare</function>. Calling
453
<function>plpy.execute</function> with a query string and an
454
optional limit argument causes that query to be run and the result
455
to be returned in a result object. The result object emulates a
456
list or dictionary object. The result object can be accessed by
457
row number and column name. It has these additional methods:
458
<function>nrows</function> which returns the number of rows
459
returned by the query, and <function>status</function> which is the
460
<function>SPI_execute()</function> return value. The result object
467
rv = plpy.execute("SELECT * FROM my_table", 5)
469
returns up to 5 rows from <literal>my_table</literal>. If
470
<literal>my_table</literal> has a column
471
<literal>my_column</literal>, it would be accessed as:
473
foo = rv[i]["my_column"]
478
<indexterm><primary>preparing a query</><secondary>in PL/Python</></indexterm>
479
The second function, <function>plpy.prepare</function>, prepares
480
the execution plan for a query. It is called with a query string
481
and a list of parameter types, if you have parameter references in
482
the query. For example:
484
plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])
486
<literal>text</literal> is the type of the variable you will be
487
passing for <literal>$1</literal>. After preparing a statement, you
488
use the function <function>plpy.execute</function> to run it:
490
rv = plpy.execute(plan, [ "name" ], 5)
492
The third argument is the limit and is optional.
496
When you prepare a plan using the PL/Python module it is
497
automatically saved. Read the SPI documentation (<xref
498
linkend="spi">) for a description of what this means.
499
In order to make effective use of this across function calls
500
one needs to use one of the persistent storage dictionaries
501
<literal>SD</literal> or <literal>GD</literal> (see
502
<xref linkend="plpython-funcs">). For example:
504
CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
505
if SD.has_key("plan"):
508
plan = plpy.prepare("SELECT 1")
511
$$ LANGUAGE plpythonu;
517
<!-- NOT CURRENTLY SUPPORTED -->
519
<sect1 id="plpython-trusted">
520
<title>Restricted Environment</title>
523
The current version of <application>PL/Python</application>
524
functions as a trusted language only; access to the file system and
525
other local resources is disabled. Specifically,
526
<application>PL/Python</application> uses the Python restricted
527
execution environment, further restricts it to prevent the use of
528
the file <function>open</> call, and allows only modules from a
529
specific list to be imported. Presently, that list includes:
530
<literal>array</>, <literal>bisect</>, <literal>binascii</>,
531
<literal>calendar</>, <literal>cmath</>, <literal>codecs</>,
532
<literal>errno</>, <literal>marshal</>, <literal>math</>, <literal>md5</>,
533
<literal>mpz</>, <literal>operator</>, <literal>pcre</>,
534
<literal>pickle</>, <literal>random</>, <literal>re</>, <literal>regex</>,
535
<literal>sre</>, <literal>sha</>, <literal>string</>, <literal>StringIO</>,
536
<literal>struct</>, <literal>time</>, <literal>whrandom</>, and