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 Query parameters and result row fields are converted between PostgreSQL
1051 and Python data types as described in <xref linkend="plpython-data">.
1055 When you prepare a plan using the PL/Python module it is automatically
1056 saved. Read the SPI documentation (<xref linkend="spi">) for a
1057 description of what this means. In order to make effective use of this
1058 across function calls one needs to use one of the persistent storage
1059 dictionaries <literal>SD</literal> or <literal>GD</literal> (see
1060 <xref linkend="plpython-sharing">). For example:
1062 CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
1066 plan = plpy.prepare("SELECT 1")
1069 $$ LANGUAGE plpythonu;
1076 <term><literal>plpy.<function>cursor</function>(<replaceable>query</replaceable>)</literal></term>
1077 <term><literal>plpy.<function>cursor</function>(<replaceable>plan</replaceable> [, <replaceable>arguments</replaceable>])</literal></term>
1080 The <literal>plpy.cursor</literal> function accepts the same arguments
1081 as <literal>plpy.execute</literal> (except for the row limit) and returns
1082 a cursor object, which allows you to process large result sets in smaller
1083 chunks. As with <literal>plpy.execute</literal>, either a query string
1084 or a plan object along with a list of arguments can be used.
1088 The cursor object provides a <literal>fetch</literal> method that accepts
1089 an integer parameter and returns a result object. Each time you
1090 call <literal>fetch</literal>, the returned object will contain the next
1091 batch of rows, never larger than the parameter value. Once all rows are
1092 exhausted, <literal>fetch</literal> starts returning an empty result
1093 object. Cursor objects also provide an
1094 <ulink url="http://docs.python.org/library/stdtypes.html#iterator-types">iterator
1095 interface</ulink>, yielding one row at a time until all rows are
1096 exhausted. Data fetched that way is not returned as result objects, but
1097 rather as dictionaries, each dictionary corresponding to a single result
1102 An example of two ways of processing data from a large table is:
1104 CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
1106 for row in plpy.cursor("select num from largetable"):
1110 $$ LANGUAGE plpythonu;
1112 CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
1114 cursor = plpy.cursor("select num from largetable")
1116 rows = cursor.fetch(batch_size)
1123 $$ LANGUAGE plpythonu;
1125 CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
1127 plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])
1128 rows = list(plpy.cursor(plan, [2]))
1131 $$ LANGUAGE plpythonu;
1136 Cursors are automatically disposed of. But if you want to explicitly
1137 release all resources held by a cursor, use the <literal>close</literal>
1138 method. Once closed, a cursor cannot be fetched from anymore.
1143 Do not confuse objects created by <literal>plpy.cursor</literal> with
1144 DB-API cursors as defined by
1145 the <ulink url="http://www.python.org/dev/peps/pep-0249/">Python
1146 Database API specification</ulink>. They don't have anything in common
1147 except for the name.
1156 <sect2 id="plpython-trapping">
1157 <title>Trapping Errors</title>
1160 Functions accessing the database might encounter errors, which
1161 will cause them to abort and raise an exception. Both
1162 <function>plpy.execute</function> and
1163 <function>plpy.prepare</function> can raise an instance of a subclass of
1164 <literal>plpy.SPIError</literal>, which by default will terminate
1165 the function. This error can be handled just like any other
1166 Python exception, by using the <literal>try/except</literal>
1167 construct. For example:
1169 CREATE FUNCTION try_adding_joe() RETURNS text AS $$
1171 plpy.execute("INSERT INTO users(username) VALUES ('joe')")
1172 except plpy.SPIError:
1173 return "something went wrong"
1176 $$ LANGUAGE plpythonu;
1181 The actual class of the exception being raised corresponds to the
1182 specific condition that caused the error. Refer
1183 to <xref linkend="errcodes-table"> for a list of possible
1184 conditions. The module
1185 <literal>plpy.spiexceptions</literal> defines an exception class
1186 for each <productname>PostgreSQL</productname> condition, deriving
1187 their names from the condition name. For
1188 instance, <literal>division_by_zero</literal>
1189 becomes <literal>DivisionByZero</literal>, <literal>unique_violation</literal>
1190 becomes <literal>UniqueViolation</literal>, <literal>fdw_error</literal>
1191 becomes <literal>FdwError</literal>, and so on. Each of these
1192 exception classes inherits from <literal>SPIError</literal>. This
1193 separation makes it easier to handle specific errors, for
1196 CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
1197 from plpy import spiexceptions
1199 plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
1200 plpy.execute(plan, [numerator, denominator])
1201 except spiexceptions.DivisionByZero:
1202 return "denominator cannot equal zero"
1203 except spiexceptions.UniqueViolation:
1204 return "already have that fraction"
1205 except plpy.SPIError, e:
1206 return "other error, SQLSTATE %s" % e.sqlstate
1208 return "fraction inserted"
1209 $$ LANGUAGE plpythonu;
1211 Note that because all exceptions from
1212 the <literal>plpy.spiexceptions</literal> module inherit
1213 from <literal>SPIError</literal>, an <literal>except</literal>
1214 clause handling it will catch any database access error.
1218 As an alternative way of handling different error conditions, you
1219 can catch the <literal>SPIError</literal> exception and determine
1220 the specific error condition inside the <literal>except</literal>
1221 block by looking at the <literal>sqlstate</literal> attribute of
1222 the exception object. This attribute is a string value containing
1223 the <quote>SQLSTATE</quote> error code. This approach provides
1224 approximately the same functionality
1229 <sect1 id="plpython-subtransaction">
1230 <title>Explicit Subtransactions</title>
1233 Recovering from errors caused by database access as described in
1234 <xref linkend="plpython-trapping"> can lead to an undesirable
1235 situation where some operations succeed before one of them fails,
1236 and after recovering from that error the data is left in an
1237 inconsistent state. PL/Python offers a solution to this problem in
1238 the form of explicit subtransactions.
1242 <title>Subtransaction Context Managers</title>
1245 Consider a function that implements a transfer between two
1248 CREATE FUNCTION transfer_funds() RETURNS void AS $$
1250 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1251 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1252 except plpy.SPIError, e:
1253 result = "error transferring funds: %s" % e.args
1255 result = "funds transferred correctly"
1256 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1257 plpy.execute(plan, [result])
1258 $$ LANGUAGE plpythonu;
1260 If the second <literal>UPDATE</literal> statement results in an
1261 exception being raised, this function will report the error, but
1262 the result of the first <literal>UPDATE</literal> will
1263 nevertheless be committed. In other words, the funds will be
1264 withdrawn from Joe's account, but will not be transferred to
1269 To avoid such issues, you can wrap your
1270 <literal>plpy.execute</literal> calls in an explicit
1271 subtransaction. The <literal>plpy</literal> module provides a
1272 helper object to manage explicit subtransactions that gets created
1273 with the <literal>plpy.subtransaction()</literal> function.
1274 Objects created by this function implement the
1275 <ulink url="http://docs.python.org/library/stdtypes.html#context-manager-types">
1276 context manager interface</ulink>. Using explicit subtransactions
1277 we can rewrite our function as:
1279 CREATE FUNCTION transfer_funds2() RETURNS void AS $$
1281 with plpy.subtransaction():
1282 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1283 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1284 except plpy.SPIError, e:
1285 result = "error transferring funds: %s" % e.args
1287 result = "funds transferred correctly"
1288 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1289 plpy.execute(plan, [result])
1290 $$ LANGUAGE plpythonu;
1292 Note that the use of <literal>try/catch</literal> is still
1293 required. Otherwise the exception would propagate to the top of
1294 the Python stack and would cause the whole function to abort with
1295 a <productname>PostgreSQL</productname> error, so that the
1296 <literal>operations</literal> table would not have any row
1297 inserted into it. The subtransaction context manager does not
1298 trap errors, it only assures that all database operations executed
1299 inside its scope will be atomically committed or rolled back. A
1300 rollback of the subtransaction block occurs on any kind of
1301 exception exit, not only ones caused by errors originating from
1302 database access. A regular Python exception raised inside an
1303 explicit subtransaction block would also cause the subtransaction
1309 <title>Older Python Versions</title>
1312 Context managers syntax using the <literal>with</literal> keyword
1313 is available by default in Python 2.6. If using PL/Python with an
1314 older Python version, it is still possible to use explicit
1315 subtransactions, although not as transparently. You can call the
1316 subtransaction manager's <literal>__enter__</literal> and
1317 <literal>__exit__</literal> functions using the
1318 <literal>enter</literal> and <literal>exit</literal> convenience
1319 aliases. The example function that transfers funds could be
1322 CREATE FUNCTION transfer_funds_old() RETURNS void AS $$
1324 subxact = plpy.subtransaction()
1327 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1328 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1331 subxact.exit(*sys.exc_info())
1334 subxact.exit(None, None, None)
1335 except plpy.SPIError, e:
1336 result = "error transferring funds: %s" % e.args
1338 result = "funds transferred correctly"
1340 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1341 plpy.execute(plan, [result])
1342 $$ LANGUAGE plpythonu;
1348 Although context managers were implemented in Python 2.5, to use
1349 the <literal>with</literal> syntax in that version you need to
1351 url="http://docs.python.org/release/2.5/ref/future.html">future
1352 statement</ulink>. Because of implementation details, however,
1353 you cannot use future statements in PL/Python functions.
1359 <sect1 id="plpython-util">
1360 <title>Utility Functions</title>
1362 The <literal>plpy</literal> module also provides the functions
1364 <member><literal>plpy.debug(<replaceable>msg, **kwargs</>)</literal></member>
1365 <member><literal>plpy.log(<replaceable>msg, **kwargs</>)</literal></member>
1366 <member><literal>plpy.info(<replaceable>msg, **kwargs</>)</literal></member>
1367 <member><literal>plpy.notice(<replaceable>msg, **kwargs</>)</literal></member>
1368 <member><literal>plpy.warning(<replaceable>msg, **kwargs</>)</literal></member>
1369 <member><literal>plpy.error(<replaceable>msg, **kwargs</>)</literal></member>
1370 <member><literal>plpy.fatal(<replaceable>msg, **kwargs</>)</literal></member>
1372 <indexterm><primary>elog</><secondary>in PL/Python</></indexterm>
1373 <function>plpy.error</function> and <function>plpy.fatal</function>
1374 actually raise a Python exception which, if uncaught, propagates out to
1375 the calling query, causing the current transaction or subtransaction to
1376 be aborted. <literal>raise plpy.Error(<replaceable>msg</>)</literal> and
1377 <literal>raise plpy.Fatal(<replaceable>msg</>)</literal> are
1378 equivalent to calling <literal>plpy.error(<replaceable>msg</>)</literal> and
1379 <literal>plpy.fatal(<replaceable>msg</>)</literal>, respectively but
1380 the <literal>raise</literal> form does not allow passing keyword arguments.
1381 The other functions only generate messages of different priority levels.
1382 Whether messages of a particular priority are reported to the client,
1383 written to the server log, or both is controlled by the
1384 <xref linkend="guc-log-min-messages"> and
1385 <xref linkend="guc-client-min-messages"> configuration
1386 variables. See <xref linkend="runtime-config"> for more information.
1390 The <replaceable>msg</> argument is given as a positional argument. For
1391 backward compatibility, more than one positional argument can be given. In
1392 that case, the string representation of the tuple of positional arguments
1393 becomes the message reported to the client.
1397 The following keyword-only arguments are accepted:
1399 <member><literal>detail</literal></member>
1400 <member><literal>hint</literal></member>
1401 <member><literal>sqlstate</literal></member>
1402 <member><literal>schema_name</literal></member>
1403 <member><literal>table_name</literal></member>
1404 <member><literal>column_name</literal></member>
1405 <member><literal>datatype_name</literal></member>
1406 <member><literal>constraint_name</literal></member>
1408 The string representation of the objects passed as keyword-only arguments
1409 is used to enrich the messages reported to the client. For example:
1412 CREATE FUNCTION raise_custom_exception() RETURNS void AS $$
1413 plpy.error("custom exception message",
1414 detail="some info about exception",
1415 hint="hint for users")
1416 $$ LANGUAGE plpythonu;
1418 =# SELECT raise_custom_exception();
1419 ERROR: plpy.Error: custom exception message
1420 DETAIL: some info about exception
1421 HINT: hint for users
1422 CONTEXT: Traceback (most recent call last):
1423 PL/Python function "raise_custom_exception", line 4, in <module>
1424 hint="hint for users")
1425 PL/Python function "raise_custom_exception"
1430 Another set of utility functions are
1431 <literal>plpy.quote_literal(<replaceable>string</>)</literal>,
1432 <literal>plpy.quote_nullable(<replaceable>string</>)</literal>, and
1433 <literal>plpy.quote_ident(<replaceable>string</>)</literal>. They
1434 are equivalent to the built-in quoting functions described in <xref
1435 linkend="functions-string">. They are useful when constructing
1436 ad-hoc queries. A PL/Python equivalent of dynamic SQL from <xref
1437 linkend="plpgsql-quote-literal-example"> would be:
1439 plpy.execute("UPDATE tbl SET %s = %s WHERE key = %s" % (
1440 plpy.quote_ident(colname),
1441 plpy.quote_nullable(newvalue),
1442 plpy.quote_literal(keyvalue)))
1447 <sect1 id="plpython-envar">
1448 <title>Environment Variables</title>
1451 Some of the environment variables that are accepted by the Python
1452 interpreter can also be used to affect PL/Python behavior. They
1453 would need to be set in the environment of the main PostgreSQL
1454 server process, for example in a start script. The available
1455 environment variables depend on the version of Python; see the
1456 Python documentation for details. At the time of this writing, the
1457 following environment variables have an affect on PL/Python,
1458 assuming an adequate Python version:
1461 <para><envar>PYTHONHOME</envar></para>
1465 <para><envar>PYTHONPATH</envar></para>
1469 <para><envar>PYTHONY2K</envar></para>
1473 <para><envar>PYTHONOPTIMIZE</envar></para>
1477 <para><envar>PYTHONDEBUG</envar></para>
1481 <para><envar>PYTHONVERBOSE</envar></para>
1485 <para><envar>PYTHONCASEOK</envar></para>
1489 <para><envar>PYTHONDONTWRITEBYTECODE</envar></para>
1493 <para><envar>PYTHONIOENCODING</envar></para>
1497 <para><envar>PYTHONUSERBASE</envar></para>
1501 <para><envar>PYTHONHASHSEED</envar></para>
1505 (It appears to be a Python implementation detail beyond the control
1506 of PL/Python that some of the environment variables listed on
1507 the <command>python</command> man page are only effective in a
1508 command-line interpreter and not an embedded Python interpreter.)