1 <!-- doc/src/sgml/plpython.sgml -->
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>CREATE EXTENSION plpythonu</> (but
18 see also <xref linkend="plpython-python23">).
23 If a language is installed into <literal>template1</>, all subsequently
24 created databases will have the language installed automatically.
29 PL/Python is only available as an <quote>untrusted</> language, meaning
30 it does not offer any way of restricting what users can do in it and
31 is therefore named <literal>plpythonu</>. A trusted
32 variant <literal>plpython</> might become available in the future
33 if a secure execution mechanism is developed in Python. The
34 writer of a function in untrusted PL/Python must take care that the
35 function cannot be used to do anything unwanted, since it will be
36 able to do anything that could be done by a user logged in as the
37 database administrator. Only superusers can create functions in
38 untrusted languages such as <literal>plpythonu</literal>.
43 Users of source packages must specially enable the build of
44 PL/Python during the installation process. (Refer to the
45 installation instructions for more information.) Users of binary
46 packages might find PL/Python in a separate subpackage.
50 <sect1 id="plpython-python23">
51 <title>Python 2 vs. Python 3</title>
54 PL/Python supports both the Python 2 and Python 3 language
55 variants. (The PostgreSQL installation instructions might contain
56 more precise information about the exact supported minor versions
57 of Python.) Because the Python 2 and Python 3 language variants
58 are incompatible in some important aspects, the following naming
59 and transitioning scheme is used by PL/Python to avoid mixing them:
64 The PostgreSQL language named <literal>plpython2u</literal>
65 implements PL/Python based on the Python 2 language variant.
71 The PostgreSQL language named <literal>plpython3u</literal>
72 implements PL/Python based on the Python 3 language variant.
78 The language named <literal>plpythonu</literal> implements
79 PL/Python based on the default Python language variant, which is
80 currently Python 2. (This default is independent of what any
81 local Python installations might consider to be
82 their <quote>default</quote>, for example,
83 what <filename>/usr/bin/python</filename> might be.) The
84 default will probably be changed to Python 3 in a distant future
85 release of PostgreSQL, depending on the progress of the
86 migration to Python 3 in the Python community.
91 This scheme is analogous to the recommendations in <ulink
92 url="http://www.python.org/dev/peps/pep-0394/">PEP 394</ulink> regarding the
93 naming and transitioning of the <command>python</command> command.
97 It depends on the build configuration or the installed packages
98 whether PL/Python for Python 2 or Python 3 or both are available.
103 The built variant depends on which Python version was found during
104 the installation or which version was explicitly set using
105 the <envar>PYTHON</envar> environment variable;
106 see <xref linkend="install-procedure">. To make both variants of
107 PL/Python available in one installation, the source tree has to be
108 configured and built twice.
113 This results in the following usage and migration strategy:
118 Existing users and users who are currently not interested in
119 Python 3 use the language name <literal>plpythonu</literal> and
120 don't have to change anything for the foreseeable future. It is
121 recommended to gradually <quote>future-proof</quote> the code
122 via migration to Python 2.6/2.7 to simplify the eventual
123 migration to Python 3.
127 In practice, many PL/Python functions will migrate to Python 3
128 with few or no changes.
134 Users who know that they have heavily Python 2 dependent code
135 and don't plan to ever change it can make use of
136 the <literal>plpython2u</literal> language name. This will
137 continue to work into the very distant future, until Python 2
138 support might be completely dropped by PostgreSQL.
144 Users who want to dive into Python 3 can use
145 the <literal>plpython3u</literal> language name, which will keep
146 working forever by today's standards. In the distant future,
147 when Python 3 might become the default, they might like to
148 remove the <quote>3</quote> for aesthetic reasons.
154 Daredevils, who want to build a Python-3-only operating system
155 environment, can change the contents of
156 <link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link>
157 to make <literal>plpythonu</literal> be equivalent
158 to <literal>plpython3u</literal>, keeping in mind that this
159 would make their installation incompatible with most of the rest
168 document <ulink url="http://docs.python.org/py3k/whatsnew/3.0.html">What's
169 New In Python 3.0</ulink> for more information about porting to
174 It is not allowed to use PL/Python based on Python 2 and PL/Python
175 based on Python 3 in the same session, because the symbols in the
176 dynamic modules would clash, which could result in crashes of the
177 PostgreSQL server process. There is a check that prevents mixing
178 Python major versions in a session, which will abort the session if
179 a mismatch is detected. It is possible, however, to use both
180 PL/Python variants in the same database, from separate sessions.
184 <sect1 id="plpython-funcs">
185 <title>PL/Python Functions</title>
188 Functions in PL/Python are declared via the
189 standard <xref linkend="sql-createfunction"> syntax:
192 CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-list</replaceable>)
193 RETURNS <replaceable>return-type</replaceable>
195 # PL/Python function body
196 $$ LANGUAGE plpythonu;
201 The body of a function is simply a Python script. When the function
202 is called, its arguments are passed as elements of the list
203 <varname>args</varname>; named arguments are also passed as
204 ordinary variables to the Python script. Use of named arguments is
205 usually more readable. The result is returned from the Python code
206 in the usual way, with <literal>return</literal> or
207 <literal>yield</literal> (in case of a result-set statement). If
208 you do not provide a return value, Python returns the default
209 <symbol>None</symbol>. <application>PL/Python</application> translates
210 Python's <symbol>None</symbol> into the SQL null value.
214 For example, a function to return the greater of two integers can be
218 CREATE FUNCTION pymax (a integer, b integer)
224 $$ LANGUAGE plpythonu;
227 The Python code that is given as the body of the function definition
228 is transformed into a Python function. For example, the above results in:
231 def __plpython_procedure_pymax_23456():
237 assuming that 23456 is the OID assigned to the function by
238 <productname>PostgreSQL</productname>.
242 The arguments are set as global variables. Because of the scoping
243 rules of Python, this has the subtle consequence that an argument
244 variable cannot be reassigned inside the function to the value of
245 an expression that involves the variable name itself, unless the
246 variable is redeclared as global in the block. For example, the
247 following won't work:
249 CREATE FUNCTION pystrip(x text)
252 x = x.strip() # error
254 $$ LANGUAGE plpythonu;
256 because assigning to <varname>x</varname>
257 makes <varname>x</varname> a local variable for the entire block,
258 and so the <varname>x</varname> on the right-hand side of the
259 assignment refers to a not-yet-assigned local
260 variable <varname>x</varname>, not the PL/Python function
261 parameter. Using the <literal>global</literal> statement, this can
264 CREATE FUNCTION pystrip(x text)
268 x = x.strip() # ok now
270 $$ LANGUAGE plpythonu;
272 But it is advisable not to rely on this implementation detail of
273 PL/Python. It is better to treat the function parameters as
278 <sect1 id="plpython-data">
279 <title>Data Values</title>
281 Generally speaking, the aim of PL/Python is to provide
282 a <quote>natural</quote> mapping between the PostgreSQL and the
283 Python worlds. This informs the data mapping rules described
288 <title>Data Type Mapping</title>
290 When a PL/Python function is called, its arguments are converted from
291 their PostgreSQL data type to a corresponding Python type:
296 PostgreSQL <type>boolean</type> is converted to Python <type>bool</type>.
302 PostgreSQL <type>smallint</type> and <type>int</type> are
303 converted to Python <type>int</type>.
304 PostgreSQL <type>bigint</type> and <type>oid</type> are converted
305 to <type>long</type> in Python 2 and to <type>int</type> in
312 PostgreSQL <type>real</type> and <type>double</type> are converted to
313 Python <type>float</type>.
319 PostgreSQL <type>numeric</type> is converted to
320 Python <type>Decimal</type>. This type is imported from
321 the <literal>cdecimal</literal> package if that is available.
323 <literal>decimal.Decimal</literal> from the standard library will be
324 used. <literal>cdecimal</literal> is significantly faster
325 than <literal>decimal</literal>. In Python 3.3 and up,
326 however, <literal>cdecimal</literal> has been integrated into the
327 standard library under the name <literal>decimal</literal>, so there is
328 no longer any difference.
334 PostgreSQL <type>bytea</type> is converted to
335 Python <type>str</type> in Python 2 and to <type>bytes</type>
336 in Python 3. In Python 2, the string should be treated as a
337 byte sequence without any character encoding.
343 All other data types, including the PostgreSQL character string
344 types, are converted to a Python <type>str</type>. In Python
345 2, this string will be in the PostgreSQL server encoding; in
346 Python 3, it will be a Unicode string like all strings.
352 For nonscalar data types, see below.
359 When a PL/Python function returns, its return value is converted to the
360 function's declared PostgreSQL return data type as follows:
365 When the PostgreSQL return type is <type>boolean</type>, the
366 return value will be evaluated for truth according to the
367 <emphasis>Python</emphasis> rules. That is, 0 and empty string
368 are false, but notably <literal>'f'</literal> is true.
374 When the PostgreSQL return type is <type>bytea</type>, the
375 return value will be converted to a string (Python 2) or bytes
376 (Python 3) using the respective Python built-ins, with the
377 result being converted to <type>bytea</type>.
383 For all other PostgreSQL return types, the return value is converted
384 to a string using the Python built-in <literal>str</literal>, and the
385 result is passed to the input function of the PostgreSQL data type.
386 (If the Python value is a <type>float</>, it is converted using
387 the <literal>repr</> built-in instead of <literal>str</literal>, to
388 avoid loss of precision.)
392 Strings in Python 2 are required to be in the PostgreSQL server
393 encoding when they are passed to PostgreSQL. Strings that are
394 not valid in the current server encoding will raise an error,
395 but not all encoding mismatches can be detected, so garbage
396 data can still result when this is not done correctly. Unicode
397 strings are converted to the correct encoding automatically, so
398 it can be safer and more convenient to use those. In Python 3,
399 all strings are Unicode strings.
405 For nonscalar data types, see below.
410 Note that logical mismatches between the declared PostgreSQL
411 return type and the Python data type of the actual return object
412 are not flagged; the value will be converted in any case.
417 <title>Null, None</title>
419 If an SQL null value<indexterm><primary>null value</primary><secondary
420 sortas="PL/Python">in PL/Python</secondary></indexterm> is passed to a
421 function, the argument value will appear as <symbol>None</symbol> in
422 Python. For example, the function definition of <function>pymax</function>
423 shown in <xref linkend="plpython-funcs"> will return the wrong answer for null
424 inputs. We could add <literal>STRICT</literal> to the function definition
425 to make <productname>PostgreSQL</productname> do something more reasonable:
426 if a null value is passed, the function will not be called at all,
427 but will just return a null result automatically. Alternatively,
428 we could check for null inputs in the function body:
431 CREATE FUNCTION pymax (a integer, b integer)
434 if (a is None) or (b is None):
439 $$ LANGUAGE plpythonu;
442 As shown above, to return an SQL null value from a PL/Python
443 function, return the value <symbol>None</symbol>. This can be done whether the
444 function is strict or not.
448 <sect2 id="plpython-arrays">
449 <title>Arrays, Lists</title>
451 SQL array values are passed into PL/Python as a Python list. To
452 return an SQL array value out of a PL/Python function, return a
456 CREATE FUNCTION return_arr()
459 return [1, 2, 3, 4, 5]
460 $$ LANGUAGE plpythonu;
469 Multidimensional arrays are passed into PL/Python as nested Python lists.
470 A 2-dimensional array is a list of lists, for example. When returning
471 a multi-dimensional SQL array out of a PL/Python function, the inner
472 lists at each level must all be of the same size. For example:
475 CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$
476 plpy.info(x, type(x))
478 $$ LANGUAGE plpythonu;
480 SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
481 INFO: ([[1, 2, 3], [4, 5, 6]], <type 'list'>)
482 test_type_conversion_array_int4
483 ---------------------------------
488 Other Python sequences, like tuples, are also accepted for
489 backwards-compatibility with PostgreSQL versions 9.6 and below, when
490 multi-dimensional arrays were not supported. However, they are always
491 treated as one-dimensional arrays, because they are ambiguous with
492 composite types. For the same reason, when a composite type is used in a
493 multi-dimensional array, it must be represented by a tuple, rather than a
497 Note that in Python, strings are sequences, which can have
498 undesirable effects that might be familiar to Python programmers:
501 CREATE FUNCTION return_str_arr()
505 $$ LANGUAGE plpythonu;
507 SELECT return_str_arr();
517 <title>Composite Types</title>
519 Composite-type arguments are passed to the function as Python mappings. The
520 element names of the mapping are the attribute names of the composite type.
521 If an attribute in the passed row has the null value, it has the value
522 <symbol>None</symbol> in the mapping. Here is an example:
525 CREATE TABLE employee (
531 CREATE FUNCTION overpaid (e employee)
534 if e["salary"] > 200000:
536 if (e["age"] < 30) and (e["salary"] > 100000):
539 $$ LANGUAGE plpythonu;
544 There are multiple ways to return row or composite types from a Python
545 function. The following examples assume we have:
548 CREATE TYPE named_value AS (
554 A composite result can be returned as a:
558 <term>Sequence type (a tuple or list, but not a set because
559 it is not indexable)</term>
562 Returned sequence objects must have the same number of items as the
563 composite result type has fields. The item with index 0 is assigned to
564 the first field of the composite type, 1 to the second and so on. For
568 CREATE FUNCTION make_pair (name text, value integer)
571 return ( name, value )
572 # or alternatively, as tuple: return [ name, value ]
573 $$ LANGUAGE plpythonu;
576 To return a SQL null for any column, insert <symbol>None</symbol> at
577 the corresponding position.
580 When an array of composite types is returned, it cannot be returned as a list,
581 because it is ambiguous whether the Python list represents a composite type,
582 or another array dimension.
588 <term>Mapping (dictionary)</term>
591 The value for each result type column is retrieved from the mapping
592 with the column name as key. Example:
595 CREATE FUNCTION make_pair (name text, value integer)
598 return { "name": name, "value": value }
599 $$ LANGUAGE plpythonu;
602 Any extra dictionary key/value pairs are ignored. Missing keys are
604 To return a SQL null value for any column, insert
605 <symbol>None</symbol> with the corresponding column name as the key.
611 <term>Object (any object providing method <literal>__getattr__</literal>)</term>
614 This works the same as a mapping.
618 CREATE FUNCTION make_pair (name text, value integer)
622 def __init__ (self, n, v):
625 return named_value(name, value)
632 $$ LANGUAGE plpythonu;
641 Functions with <literal>OUT</literal> parameters are also supported. For example:
643 CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$
645 $$ LANGUAGE plpythonu;
647 SELECT * FROM multiout_simple();
653 <title>Set-returning Functions</title>
655 A <application>PL/Python</application> function can also return sets of
656 scalar or composite types. There are several ways to achieve this because
657 the returned object is internally turned into an iterator. The following
658 examples assume we have composite type:
661 CREATE TYPE greeting AS (
667 A set result can be returned from a:
671 <term>Sequence type (tuple, list, set)</term>
675 CREATE FUNCTION greet (how text)
676 RETURNS SETOF greeting
678 # return tuple containing lists as composite types
679 # all other combinations work also
680 return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
681 $$ LANGUAGE plpythonu;
688 <term>Iterator (any object providing <symbol>__iter__</symbol> and
689 <symbol>next</symbol> methods)</term>
693 CREATE FUNCTION greet (how text)
694 RETURNS SETOF greeting
697 def __init__ (self, how, who):
707 if self.ndx == len(self.who):
709 return ( self.how, self.who[self.ndx] )
711 return producer(how, [ "World", "PostgreSQL", "PL/Python" ])
712 $$ LANGUAGE plpythonu;
719 <term>Generator (<literal>yield</literal>)</term>
723 CREATE FUNCTION greet (how text)
724 RETURNS SETOF greeting
726 for who in [ "World", "PostgreSQL", "PL/Python" ]:
728 $$ LANGUAGE plpythonu;
738 Set-returning functions with <literal>OUT</literal> parameters
739 (using <literal>RETURNS SETOF record</literal>) are also
740 supported. For example:
742 CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer) RETURNS SETOF record AS $$
744 $$ LANGUAGE plpythonu;
746 SELECT * FROM multiout_simple_setof(3);
752 <sect1 id="plpython-sharing">
753 <title>Sharing Data</title>
755 The global dictionary <varname>SD</varname> is available to store
756 data between function calls. This variable is private static data.
757 The global dictionary <varname>GD</varname> is public data,
758 available to all Python functions within a session. Use with
759 care.<indexterm><primary>global data</>
760 <secondary>in PL/Python</></indexterm>
764 Each function gets its own execution environment in the
765 Python interpreter, so that global data and function arguments from
766 <function>myfunc</function> are not available to
767 <function>myfunc2</function>. The exception is the data in the
768 <varname>GD</varname> dictionary, as mentioned above.
772 <sect1 id="plpython-do">
773 <title>Anonymous Code Blocks</title>
776 PL/Python also supports anonymous code blocks called with the
777 <xref linkend="sql-do"> statement:
782 $$ LANGUAGE plpythonu;
785 An anonymous code block receives no arguments, and whatever value it
786 might return is discarded. Otherwise it behaves just like a function.
790 <sect1 id="plpython-trigger">
791 <title>Trigger Functions</title>
793 <indexterm zone="plpython-trigger">
794 <primary>trigger</primary>
795 <secondary>in PL/Python</secondary>
799 When a function is used as a trigger, the dictionary
800 <literal>TD</literal> contains trigger-related values:
803 <term><literal>TD["event"]</></term>
806 contains the event as a string:
807 <literal>INSERT</>, <literal>UPDATE</>,
808 <literal>DELETE</>, or <literal>TRUNCATE</>.
814 <term><literal>TD["when"]</></term>
817 contains one of <literal>BEFORE</>, <literal>AFTER</>, or
818 <literal>INSTEAD OF</>.
824 <term><literal>TD["level"]</></term>
827 contains <literal>ROW</> or <literal>STATEMENT</>.
833 <term><literal>TD["new"]</></term>
834 <term><literal>TD["old"]</></term>
837 For a row-level trigger, one or both of these fields contain
838 the respective trigger rows, depending on the trigger event.
844 <term><literal>TD["name"]</></term>
847 contains the trigger name.
853 <term><literal>TD["table_name"]</></term>
856 contains the name of the table on which the trigger occurred.
862 <term><literal>TD["table_schema"]</></term>
865 contains the schema of the table on which the trigger occurred.
871 <term><literal>TD["relid"]</></term>
874 contains the OID of the table on which the trigger occurred.
880 <term><literal>TD["args"]</></term>
883 If the <command>CREATE TRIGGER</> command
884 included arguments, they are available in <literal>TD["args"][0]</> to
885 <literal>TD["args"][<replaceable>n</>-1]</>.
893 If <literal>TD["when"]</literal> is <literal>BEFORE</> or
894 <literal>INSTEAD OF</> and
895 <literal>TD["level"]</literal> is <literal>ROW</>, you can
896 return <literal>None</literal> or <literal>"OK"</literal> from the
897 Python function to indicate the row is unmodified,
898 <literal>"SKIP"</> to abort the event, or if <literal>TD["event"]</>
899 is <command>INSERT</> or <command>UPDATE</> you can return
900 <literal>"MODIFY"</> to indicate you've modified the new row.
901 Otherwise the return value is ignored.
905 <sect1 id="plpython-database">
906 <title>Database Access</title>
909 The PL/Python language module automatically imports a Python module
910 called <literal>plpy</literal>. The functions and constants in
911 this module are available to you in the Python code as
912 <literal>plpy.<replaceable>foo</replaceable></literal>.
916 <title>Database Access Functions</title>
919 The <literal>plpy</literal> module provides several functions to execute
925 <term><literal>plpy.<function>execute</function>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
928 Calling <function>plpy.execute</function> with a query string and an
929 optional row limit argument causes that query to be run and the result to
930 be returned in a result object.
934 The result object emulates a list or dictionary object. The result
935 object can be accessed by row number and column name. For example:
937 rv = plpy.execute("SELECT * FROM my_table", 5)
939 returns up to 5 rows from <literal>my_table</literal>. If
940 <literal>my_table</literal> has a column
941 <literal>my_column</literal>, it would be accessed as:
943 foo = rv[i]["my_column"]
945 The number of rows returned can be obtained using the built-in
946 <function>len</function> function.
950 The result object has these additional methods:
953 <term><literal><function>nrows</function>()</literal></term>
956 Returns the number of rows processed by the command. Note that this
957 is not necessarily the same as the number of rows returned. For
958 example, an <command>UPDATE</command> command will set this value but
959 won't return any rows (unless <literal>RETURNING</literal> is used).
965 <term><literal><function>status</function>()</literal></term>
968 The <function>SPI_execute()</function> return value.
974 <term><literal><function>colnames</function>()</literal></term>
975 <term><literal><function>coltypes</function>()</literal></term>
976 <term><literal><function>coltypmods</function>()</literal></term>
979 Return a list of column names, list of column type OIDs, and list of
980 type-specific type modifiers for the columns, respectively.
984 These methods raise an exception when called on a result object from
985 a command that did not produce a result set, e.g.,
986 <command>UPDATE</command> without <literal>RETURNING</literal>, or
987 <command>DROP TABLE</command>. But it is OK to use these methods on
988 a result set containing zero rows.
994 <term><literal><function>__str__</function>()</literal></term>
997 The standard <literal>__str__</literal> method is defined so that it
998 is possible for example to debug query execution results
999 using <literal>plpy.debug(rv)</literal>.
1007 The result object can be modified.
1011 Note that calling <literal>plpy.execute</literal> will cause the entire
1012 result set to be read into memory. Only use that function when you are
1013 sure that the result set will be relatively small. If you don't want to
1014 risk excessive memory usage when fetching large results,
1015 use <literal>plpy.cursor</literal> rather
1016 than <literal>plpy.execute</literal>.
1022 <term><literal>plpy.<function>prepare</function>(<replaceable>query</replaceable> [, <replaceable>argtypes</replaceable>])</literal></term>
1023 <term><literal>plpy.<function>execute</function>(<replaceable>plan</replaceable> [, <replaceable>arguments</replaceable> [, <replaceable>max-rows</replaceable>]])</literal></term>
1026 <indexterm><primary>preparing a query</><secondary>in PL/Python</></indexterm>
1027 <function>plpy.prepare</function> prepares the execution plan for a
1028 query. It is called with a query string and a list of parameter types,
1029 if you have parameter references in the query. For example:
1031 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])
1033 <literal>text</literal> is the type of the variable you will be passing
1034 for <literal>$1</literal>. The second argument is optional if you don't
1035 want to pass any parameters to the query.
1038 After preparing a statement, you use a variant of the
1039 function <function>plpy.execute</function> to run it:
1041 rv = plpy.execute(plan, ["name"], 5)
1043 Pass the plan as the first argument (instead of the query string), and a
1044 list of values to substitute into the query as the second argument. The
1045 second argument is optional if the query does not expect any parameters.
1046 The third argument is the optional row limit as before.
1050 Alternatively, you can call the <function>execute</function> method on
1053 rv = plan.execute(["name"], 5)
1058 Query parameters and result row fields are converted between PostgreSQL
1059 and Python data types as described in <xref linkend="plpython-data">.
1063 When you prepare a plan using the PL/Python module it is automatically
1064 saved. Read the SPI documentation (<xref linkend="spi">) for a
1065 description of what this means. In order to make effective use of this
1066 across function calls one needs to use one of the persistent storage
1067 dictionaries <literal>SD</literal> or <literal>GD</literal> (see
1068 <xref linkend="plpython-sharing">). For example:
1070 CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
1074 plan = plpy.prepare("SELECT 1")
1077 $$ LANGUAGE plpythonu;
1084 <term><literal>plpy.<function>cursor</function>(<replaceable>query</replaceable>)</literal></term>
1085 <term><literal>plpy.<function>cursor</function>(<replaceable>plan</replaceable> [, <replaceable>arguments</replaceable>])</literal></term>
1088 The <literal>plpy.cursor</literal> function accepts the same arguments
1089 as <literal>plpy.execute</literal> (except for the row limit) and returns
1090 a cursor object, which allows you to process large result sets in smaller
1091 chunks. As with <literal>plpy.execute</literal>, either a query string
1092 or a plan object along with a list of arguments can be used, or
1093 the <function>cursor</function> function can be called as a method of
1098 The cursor object provides a <literal>fetch</literal> method that accepts
1099 an integer parameter and returns a result object. Each time you
1100 call <literal>fetch</literal>, the returned object will contain the next
1101 batch of rows, never larger than the parameter value. Once all rows are
1102 exhausted, <literal>fetch</literal> starts returning an empty result
1103 object. Cursor objects also provide an
1104 <ulink url="http://docs.python.org/library/stdtypes.html#iterator-types">iterator
1105 interface</ulink>, yielding one row at a time until all rows are
1106 exhausted. Data fetched that way is not returned as result objects, but
1107 rather as dictionaries, each dictionary corresponding to a single result
1112 An example of two ways of processing data from a large table is:
1114 CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
1116 for row in plpy.cursor("select num from largetable"):
1120 $$ LANGUAGE plpythonu;
1122 CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
1124 cursor = plpy.cursor("select num from largetable")
1126 rows = cursor.fetch(batch_size)
1133 $$ LANGUAGE plpythonu;
1135 CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
1137 plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])
1138 rows = list(plpy.cursor(plan, [2])) # or: = list(plan.cursor([2]))
1141 $$ LANGUAGE plpythonu;
1146 Cursors are automatically disposed of. But if you want to explicitly
1147 release all resources held by a cursor, use the <literal>close</literal>
1148 method. Once closed, a cursor cannot be fetched from anymore.
1153 Do not confuse objects created by <literal>plpy.cursor</literal> with
1154 DB-API cursors as defined by
1155 the <ulink url="http://www.python.org/dev/peps/pep-0249/">Python
1156 Database API specification</ulink>. They don't have anything in common
1157 except for the name.
1166 <sect2 id="plpython-trapping">
1167 <title>Trapping Errors</title>
1170 Functions accessing the database might encounter errors, which
1171 will cause them to abort and raise an exception. Both
1172 <function>plpy.execute</function> and
1173 <function>plpy.prepare</function> can raise an instance of a subclass of
1174 <literal>plpy.SPIError</literal>, which by default will terminate
1175 the function. This error can be handled just like any other
1176 Python exception, by using the <literal>try/except</literal>
1177 construct. For example:
1179 CREATE FUNCTION try_adding_joe() RETURNS text AS $$
1181 plpy.execute("INSERT INTO users(username) VALUES ('joe')")
1182 except plpy.SPIError:
1183 return "something went wrong"
1186 $$ LANGUAGE plpythonu;
1191 The actual class of the exception being raised corresponds to the
1192 specific condition that caused the error. Refer
1193 to <xref linkend="errcodes-table"> for a list of possible
1194 conditions. The module
1195 <literal>plpy.spiexceptions</literal> defines an exception class
1196 for each <productname>PostgreSQL</productname> condition, deriving
1197 their names from the condition name. For
1198 instance, <literal>division_by_zero</literal>
1199 becomes <literal>DivisionByZero</literal>, <literal>unique_violation</literal>
1200 becomes <literal>UniqueViolation</literal>, <literal>fdw_error</literal>
1201 becomes <literal>FdwError</literal>, and so on. Each of these
1202 exception classes inherits from <literal>SPIError</literal>. This
1203 separation makes it easier to handle specific errors, for
1206 CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
1207 from plpy import spiexceptions
1209 plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
1210 plpy.execute(plan, [numerator, denominator])
1211 except spiexceptions.DivisionByZero:
1212 return "denominator cannot equal zero"
1213 except spiexceptions.UniqueViolation:
1214 return "already have that fraction"
1215 except plpy.SPIError, e:
1216 return "other error, SQLSTATE %s" % e.sqlstate
1218 return "fraction inserted"
1219 $$ LANGUAGE plpythonu;
1221 Note that because all exceptions from
1222 the <literal>plpy.spiexceptions</literal> module inherit
1223 from <literal>SPIError</literal>, an <literal>except</literal>
1224 clause handling it will catch any database access error.
1228 As an alternative way of handling different error conditions, you
1229 can catch the <literal>SPIError</literal> exception and determine
1230 the specific error condition inside the <literal>except</literal>
1231 block by looking at the <literal>sqlstate</literal> attribute of
1232 the exception object. This attribute is a string value containing
1233 the <quote>SQLSTATE</quote> error code. This approach provides
1234 approximately the same functionality
1239 <sect1 id="plpython-subtransaction">
1240 <title>Explicit Subtransactions</title>
1243 Recovering from errors caused by database access as described in
1244 <xref linkend="plpython-trapping"> can lead to an undesirable
1245 situation where some operations succeed before one of them fails,
1246 and after recovering from that error the data is left in an
1247 inconsistent state. PL/Python offers a solution to this problem in
1248 the form of explicit subtransactions.
1252 <title>Subtransaction Context Managers</title>
1255 Consider a function that implements a transfer between two
1258 CREATE FUNCTION transfer_funds() RETURNS void AS $$
1260 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1261 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1262 except plpy.SPIError, e:
1263 result = "error transferring funds: %s" % e.args
1265 result = "funds transferred correctly"
1266 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1267 plpy.execute(plan, [result])
1268 $$ LANGUAGE plpythonu;
1270 If the second <literal>UPDATE</literal> statement results in an
1271 exception being raised, this function will report the error, but
1272 the result of the first <literal>UPDATE</literal> will
1273 nevertheless be committed. In other words, the funds will be
1274 withdrawn from Joe's account, but will not be transferred to
1279 To avoid such issues, you can wrap your
1280 <literal>plpy.execute</literal> calls in an explicit
1281 subtransaction. The <literal>plpy</literal> module provides a
1282 helper object to manage explicit subtransactions that gets created
1283 with the <literal>plpy.subtransaction()</literal> function.
1284 Objects created by this function implement the
1285 <ulink url="http://docs.python.org/library/stdtypes.html#context-manager-types">
1286 context manager interface</ulink>. Using explicit subtransactions
1287 we can rewrite our function as:
1289 CREATE FUNCTION transfer_funds2() RETURNS void AS $$
1291 with plpy.subtransaction():
1292 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1293 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1294 except plpy.SPIError, e:
1295 result = "error transferring funds: %s" % e.args
1297 result = "funds transferred correctly"
1298 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1299 plpy.execute(plan, [result])
1300 $$ LANGUAGE plpythonu;
1302 Note that the use of <literal>try/catch</literal> is still
1303 required. Otherwise the exception would propagate to the top of
1304 the Python stack and would cause the whole function to abort with
1305 a <productname>PostgreSQL</productname> error, so that the
1306 <literal>operations</literal> table would not have any row
1307 inserted into it. The subtransaction context manager does not
1308 trap errors, it only assures that all database operations executed
1309 inside its scope will be atomically committed or rolled back. A
1310 rollback of the subtransaction block occurs on any kind of
1311 exception exit, not only ones caused by errors originating from
1312 database access. A regular Python exception raised inside an
1313 explicit subtransaction block would also cause the subtransaction
1319 <title>Older Python Versions</title>
1322 Context managers syntax using the <literal>with</literal> keyword
1323 is available by default in Python 2.6. If using PL/Python with an
1324 older Python version, it is still possible to use explicit
1325 subtransactions, although not as transparently. You can call the
1326 subtransaction manager's <literal>__enter__</literal> and
1327 <literal>__exit__</literal> functions using the
1328 <literal>enter</literal> and <literal>exit</literal> convenience
1329 aliases. The example function that transfers funds could be
1332 CREATE FUNCTION transfer_funds_old() RETURNS void AS $$
1334 subxact = plpy.subtransaction()
1337 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1338 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1341 subxact.exit(*sys.exc_info())
1344 subxact.exit(None, None, None)
1345 except plpy.SPIError, e:
1346 result = "error transferring funds: %s" % e.args
1348 result = "funds transferred correctly"
1350 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1351 plpy.execute(plan, [result])
1352 $$ LANGUAGE plpythonu;
1358 Although context managers were implemented in Python 2.5, to use
1359 the <literal>with</literal> syntax in that version you need to
1361 url="http://docs.python.org/release/2.5/ref/future.html">future
1362 statement</ulink>. Because of implementation details, however,
1363 you cannot use future statements in PL/Python functions.
1369 <sect1 id="plpython-util">
1370 <title>Utility Functions</title>
1372 The <literal>plpy</literal> module also provides the functions
1374 <member><literal>plpy.debug(<replaceable>msg, **kwargs</>)</literal></member>
1375 <member><literal>plpy.log(<replaceable>msg, **kwargs</>)</literal></member>
1376 <member><literal>plpy.info(<replaceable>msg, **kwargs</>)</literal></member>
1377 <member><literal>plpy.notice(<replaceable>msg, **kwargs</>)</literal></member>
1378 <member><literal>plpy.warning(<replaceable>msg, **kwargs</>)</literal></member>
1379 <member><literal>plpy.error(<replaceable>msg, **kwargs</>)</literal></member>
1380 <member><literal>plpy.fatal(<replaceable>msg, **kwargs</>)</literal></member>
1382 <indexterm><primary>elog</><secondary>in PL/Python</></indexterm>
1383 <function>plpy.error</function> and <function>plpy.fatal</function>
1384 actually raise a Python exception which, if uncaught, propagates out to
1385 the calling query, causing the current transaction or subtransaction to
1386 be aborted. <literal>raise plpy.Error(<replaceable>msg</>)</literal> and
1387 <literal>raise plpy.Fatal(<replaceable>msg</>)</literal> are
1388 equivalent to calling <literal>plpy.error(<replaceable>msg</>)</literal> and
1389 <literal>plpy.fatal(<replaceable>msg</>)</literal>, respectively but
1390 the <literal>raise</literal> form does not allow passing keyword arguments.
1391 The other functions only generate messages of different priority levels.
1392 Whether messages of a particular priority are reported to the client,
1393 written to the server log, or both is controlled by the
1394 <xref linkend="guc-log-min-messages"> and
1395 <xref linkend="guc-client-min-messages"> configuration
1396 variables. See <xref linkend="runtime-config"> for more information.
1400 The <replaceable>msg</> argument is given as a positional argument. For
1401 backward compatibility, more than one positional argument can be given. In
1402 that case, the string representation of the tuple of positional arguments
1403 becomes the message reported to the client.
1407 The following keyword-only arguments are accepted:
1409 <member><literal>detail</literal></member>
1410 <member><literal>hint</literal></member>
1411 <member><literal>sqlstate</literal></member>
1412 <member><literal>schema_name</literal></member>
1413 <member><literal>table_name</literal></member>
1414 <member><literal>column_name</literal></member>
1415 <member><literal>datatype_name</literal></member>
1416 <member><literal>constraint_name</literal></member>
1418 The string representation of the objects passed as keyword-only arguments
1419 is used to enrich the messages reported to the client. For example:
1422 CREATE FUNCTION raise_custom_exception() RETURNS void AS $$
1423 plpy.error("custom exception message",
1424 detail="some info about exception",
1425 hint="hint for users")
1426 $$ LANGUAGE plpythonu;
1428 =# SELECT raise_custom_exception();
1429 ERROR: plpy.Error: custom exception message
1430 DETAIL: some info about exception
1431 HINT: hint for users
1432 CONTEXT: Traceback (most recent call last):
1433 PL/Python function "raise_custom_exception", line 4, in <module>
1434 hint="hint for users")
1435 PL/Python function "raise_custom_exception"
1440 Another set of utility functions are
1441 <literal>plpy.quote_literal(<replaceable>string</>)</literal>,
1442 <literal>plpy.quote_nullable(<replaceable>string</>)</literal>, and
1443 <literal>plpy.quote_ident(<replaceable>string</>)</literal>. They
1444 are equivalent to the built-in quoting functions described in <xref
1445 linkend="functions-string">. They are useful when constructing
1446 ad-hoc queries. A PL/Python equivalent of dynamic SQL from <xref
1447 linkend="plpgsql-quote-literal-example"> would be:
1449 plpy.execute("UPDATE tbl SET %s = %s WHERE key = %s" % (
1450 plpy.quote_ident(colname),
1451 plpy.quote_nullable(newvalue),
1452 plpy.quote_literal(keyvalue)))
1457 <sect1 id="plpython-envar">
1458 <title>Environment Variables</title>
1461 Some of the environment variables that are accepted by the Python
1462 interpreter can also be used to affect PL/Python behavior. They
1463 would need to be set in the environment of the main PostgreSQL
1464 server process, for example in a start script. The available
1465 environment variables depend on the version of Python; see the
1466 Python documentation for details. At the time of this writing, the
1467 following environment variables have an affect on PL/Python,
1468 assuming an adequate Python version:
1471 <para><envar>PYTHONHOME</envar></para>
1475 <para><envar>PYTHONPATH</envar></para>
1479 <para><envar>PYTHONY2K</envar></para>
1483 <para><envar>PYTHONOPTIMIZE</envar></para>
1487 <para><envar>PYTHONDEBUG</envar></para>
1491 <para><envar>PYTHONVERBOSE</envar></para>
1495 <para><envar>PYTHONCASEOK</envar></para>
1499 <para><envar>PYTHONDONTWRITEBYTECODE</envar></para>
1503 <para><envar>PYTHONIOENCODING</envar></para>
1507 <para><envar>PYTHONUSERBASE</envar></para>
1511 <para><envar>PYTHONHASHSEED</envar></para>
1515 (It appears to be a Python implementation detail beyond the control
1516 of PL/Python that some of the environment variables listed on
1517 the <command>python</command> man page are only effective in a
1518 command-line interpreter and not an embedded Python interpreter.)