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</>, or from the shell command line use
18 <literal>createlang plpythonu <replaceable>dbname</></literal> (but
19 see also <xref linkend="plpython-python23">).
24 If a language is installed into <literal>template1</>, all subsequently
25 created databases will have the language installed automatically.
30 PL/Python is only available as an <quote>untrusted</> language, meaning
31 it does not offer any way of restricting what users can do in it and
32 is therefore named <literal>plpythonu</>. A trusted
33 variant <literal>plpython</> might become available in the future
34 if a secure execution mechanism is developed in Python. The
35 writer of a function in untrusted PL/Python must take care that the
36 function cannot be used to do anything unwanted, since it will be
37 able to do anything that could be done by a user logged in as the
38 database administrator. Only superusers can create functions in
39 untrusted languages such as <literal>plpythonu</literal>.
44 Users of source packages must specially enable the build of
45 PL/Python during the installation process. (Refer to the
46 installation instructions for more information.) Users of binary
47 packages might find PL/Python in a separate subpackage.
51 <sect1 id="plpython-python23">
52 <title>Python 2 vs. Python 3</title>
55 PL/Python supports both the Python 2 and Python 3 language
56 variants. (The PostgreSQL installation instructions might contain
57 more precise information about the exact supported minor versions
58 of Python.) Because the Python 2 and Python 3 language variants
59 are incompatible in some important aspects, the following naming
60 and transitioning scheme is used by PL/Python to avoid mixing them:
65 The PostgreSQL language named <literal>plpython2u</literal>
66 implements PL/Python based on the Python 2 language variant.
72 The PostgreSQL language named <literal>plpython3u</literal>
73 implements PL/Python based on the Python 3 language variant.
79 The language named <literal>plpythonu</literal> implements
80 PL/Python based on the default Python language variant, which is
81 currently Python 2. (This default is independent of what any
82 local Python installations might consider to be
83 their <quote>default</quote>, for example,
84 what <filename>/usr/bin/python</filename> might be.) The
85 default will probably be changed to Python 3 in a distant future
86 release of PostgreSQL, depending on the progress of the
87 migration to Python 3 in the Python community.
92 This scheme is analogous to the recommendations in <ulink
93 url="http://www.python.org/dev/peps/pep-0394/">PEP 394</ulink> regarding the
94 naming and transitioning of the <command>python</command> command.
98 It depends on the build configuration or the installed packages
99 whether PL/Python for Python 2 or Python 3 or both are available.
104 The built variant depends on which Python version was found during
105 the installation or which version was explicitly set using
106 the <envar>PYTHON</envar> environment variable;
107 see <xref linkend="install-procedure">. To make both variants of
108 PL/Python available in one installation, the source tree has to be
109 configured and built twice.
114 This results in the following usage and migration strategy:
119 Existing users and users who are currently not interested in
120 Python 3 use the language name <literal>plpythonu</literal> and
121 don't have to change anything for the foreseeable future. It is
122 recommended to gradually <quote>future-proof</quote> the code
123 via migration to Python 2.6/2.7 to simplify the eventual
124 migration to Python 3.
128 In practice, many PL/Python functions will migrate to Python 3
129 with few or no changes.
135 Users who know that they have heavily Python 2 dependent code
136 and don't plan to ever change it can make use of
137 the <literal>plpython2u</literal> language name. This will
138 continue to work into the very distant future, until Python 2
139 support might be completely dropped by PostgreSQL.
145 Users who want to dive into Python 3 can use
146 the <literal>plpython3u</literal> language name, which will keep
147 working forever by today's standards. In the distant future,
148 when Python 3 might become the default, they might like to
149 remove the <quote>3</quote> for aesthetic reasons.
155 Daredevils, who want to build a Python-3-only operating system
156 environment, can change the contents of
157 <link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link>
158 to make <literal>plpythonu</literal> be equivalent
159 to <literal>plpython3u</literal>, keeping in mind that this
160 would make their installation incompatible with most of the rest
169 document <ulink url="http://docs.python.org/py3k/whatsnew/3.0.html">What's
170 New In Python 3.0</ulink> for more information about porting to
175 It is not allowed to use PL/Python based on Python 2 and PL/Python
176 based on Python 3 in the same session, because the symbols in the
177 dynamic modules would clash, which could result in crashes of the
178 PostgreSQL server process. There is a check that prevents mixing
179 Python major versions in a session, which will abort the session if
180 a mismatch is detected. It is possible, however, to use both
181 PL/Python variants in the same database, from separate sessions.
185 <sect1 id="plpython-funcs">
186 <title>PL/Python Functions</title>
189 Functions in PL/Python are declared via the
190 standard <xref linkend="sql-createfunction"> syntax:
193 CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-list</replaceable>)
194 RETURNS <replaceable>return-type</replaceable>
196 # PL/Python function body
197 $$ LANGUAGE plpythonu;
202 The body of a function is simply a Python script. When the function
203 is called, its arguments are passed as elements of the list
204 <varname>args</varname>; named arguments are also passed as
205 ordinary variables to the Python script. Use of named arguments is
206 usually more readable. The result is returned from the Python code
207 in the usual way, with <literal>return</literal> or
208 <literal>yield</literal> (in case of a result-set statement). If
209 you do not provide a return value, Python returns the default
210 <symbol>None</symbol>. <application>PL/Python</application> translates
211 Python's <symbol>None</symbol> into the SQL null value.
215 For example, a function to return the greater of two integers can be
219 CREATE FUNCTION pymax (a integer, b integer)
225 $$ LANGUAGE plpythonu;
228 The Python code that is given as the body of the function definition
229 is transformed into a Python function. For example, the above results in:
232 def __plpython_procedure_pymax_23456():
238 assuming that 23456 is the OID assigned to the function by
239 <productname>PostgreSQL</productname>.
243 The arguments are set as global variables. Because of the scoping
244 rules of Python, this has the subtle consequence that an argument
245 variable cannot be reassigned inside the function to the value of
246 an expression that involves the variable name itself, unless the
247 variable is redeclared as global in the block. For example, the
248 following won't work:
250 CREATE FUNCTION pystrip(x text)
253 x = x.strip() # error
255 $$ LANGUAGE plpythonu;
257 because assigning to <varname>x</varname>
258 makes <varname>x</varname> a local variable for the entire block,
259 and so the <varname>x</varname> on the right-hand side of the
260 assignment refers to a not-yet-assigned local
261 variable <varname>x</varname>, not the PL/Python function
262 parameter. Using the <literal>global</literal> statement, this can
265 CREATE FUNCTION pystrip(x text)
269 x = x.strip() # ok now
271 $$ LANGUAGE plpythonu;
273 But it is advisable not to rely on this implementation detail of
274 PL/Python. It is better to treat the function parameters as
279 <sect1 id="plpython-data">
280 <title>Data Values</title>
282 Generally speaking, the aim of PL/Python is to provide
283 a <quote>natural</quote> mapping between the PostgreSQL and the
284 Python worlds. This informs the data mapping rules described
289 <title>Data Type Mapping</title>
291 Function arguments are converted from their PostgreSQL type to a
292 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,
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 Function return values are converted to the declared PostgreSQL
360 return data type as follows:
364 When the PostgreSQL return type is <type>boolean</type>, the
365 return value will be evaluated for truth according to the
366 <emphasis>Python</emphasis> rules. That is, 0 and empty string
367 are false, but notably <literal>'f'</literal> is true.
373 When the PostgreSQL return type is <type>bytea</type>, the
374 return value will be converted to a string (Python 2) or bytes
375 (Python 3) using the respective Python built-ins, with the
376 result being converted <type>bytea</type>.
382 For all other PostgreSQL return types, the returned Python
383 value is converted to a string using the Python
384 built-in <literal>str</literal>, and the result is passed to the
385 input function of the PostgreSQL data type.
389 Strings in Python 2 are required to be in the PostgreSQL server
390 encoding when they are passed to PostgreSQL. Strings that are
391 not valid in the current server encoding will raise an error,
392 but not all encoding mismatches can be detected, so garbage
393 data can still result when this is not done correctly. Unicode
394 strings are converted to the correct encoding automatically, so
395 it can be safer and more convenient to use those. In Python 3,
396 all strings are Unicode strings.
402 For nonscalar data types, see below.
407 Note that logical mismatches between the declared PostgreSQL
408 return type and the Python data type of the actual return object
409 are not flagged; the value will be converted in any case.
414 <title>Null, None</title>
416 If an SQL null value<indexterm><primary>null value</primary><secondary
417 sortas="PL/Python">in PL/Python</secondary></indexterm> is passed to a
418 function, the argument value will appear as <symbol>None</symbol> in
419 Python. For example, the function definition of <function>pymax</function>
420 shown in <xref linkend="plpython-funcs"> will return the wrong answer for null
421 inputs. We could add <literal>STRICT</literal> to the function definition
422 to make <productname>PostgreSQL</productname> do something more reasonable:
423 if a null value is passed, the function will not be called at all,
424 but will just return a null result automatically. Alternatively,
425 we could check for null inputs in the function body:
428 CREATE FUNCTION pymax (a integer, b integer)
431 if (a is None) or (b is None):
436 $$ LANGUAGE plpythonu;
439 As shown above, to return an SQL null value from a PL/Python
440 function, return the value <symbol>None</symbol>. This can be done whether the
441 function is strict or not.
445 <sect2 id="plpython-arrays">
446 <title>Arrays, Lists</title>
448 SQL array values are passed into PL/Python as a Python list. To
449 return an SQL array value out of a PL/Python function, return a
450 Python sequence, for example a list or tuple:
453 CREATE FUNCTION return_arr()
456 return (1, 2, 3, 4, 5)
457 $$ LANGUAGE plpythonu;
466 Note that in Python, strings are sequences, which can have
467 undesirable effects that might be familiar to Python programmers:
470 CREATE FUNCTION return_str_arr()
474 $$ LANGUAGE plpythonu;
476 SELECT return_str_arr();
486 <title>Composite Types</title>
488 Composite-type arguments are passed to the function as Python mappings. The
489 element names of the mapping are the attribute names of the composite type.
490 If an attribute in the passed row has the null value, it has the value
491 <symbol>None</symbol> in the mapping. Here is an example:
494 CREATE TABLE employee (
500 CREATE FUNCTION overpaid (e employee)
503 if e["salary"] > 200000:
505 if (e["age"] < 30) and (e["salary"] > 100000):
508 $$ LANGUAGE plpythonu;
513 There are multiple ways to return row or composite types from a Python
514 function. The following examples assume we have:
517 CREATE TYPE named_value AS (
523 A composite result can be returned as a:
527 <term>Sequence type (a tuple or list, but not a set because
528 it is not indexable)</term>
531 Returned sequence objects must have the same number of items as the
532 composite result type has fields. The item with index 0 is assigned to
533 the first field of the composite type, 1 to the second and so on. For
537 CREATE FUNCTION make_pair (name text, value integer)
540 return [ name, value ]
541 # or alternatively, as tuple: return ( name, value )
542 $$ LANGUAGE plpythonu;
545 To return a SQL null for any column, insert <symbol>None</symbol> at
546 the corresponding position.
552 <term>Mapping (dictionary)</term>
555 The value for each result type column is retrieved from the mapping
556 with the column name as key. Example:
559 CREATE FUNCTION make_pair (name text, value integer)
562 return { "name": name, "value": value }
563 $$ LANGUAGE plpythonu;
566 Any extra dictionary key/value pairs are ignored. Missing keys are
568 To return a SQL null value for any column, insert
569 <symbol>None</symbol> with the corresponding column name as the key.
575 <term>Object (any object providing method <literal>__getattr__</literal>)</term>
578 This works the same as a mapping.
582 CREATE FUNCTION make_pair (name text, value integer)
586 def __init__ (self, n, v):
589 return named_value(name, value)
596 $$ LANGUAGE plpythonu;
605 Functions with <literal>OUT</literal> parameters are also supported. For example:
607 CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$
609 $$ LANGUAGE plpythonu;
611 SELECT * FROM multiout_simple();
617 <title>Set-returning Functions</title>
619 A <application>PL/Python</application> function can also return sets of
620 scalar or composite types. There are several ways to achieve this because
621 the returned object is internally turned into an iterator. The following
622 examples assume we have composite type:
625 CREATE TYPE greeting AS (
631 A set result can be returned from a:
635 <term>Sequence type (tuple, list, set)</term>
639 CREATE FUNCTION greet (how text)
640 RETURNS SETOF greeting
642 # return tuple containing lists as composite types
643 # all other combinations work also
644 return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
645 $$ LANGUAGE plpythonu;
652 <term>Iterator (any object providing <symbol>__iter__</symbol> and
653 <symbol>next</symbol> methods)</term>
657 CREATE FUNCTION greet (how text)
658 RETURNS SETOF greeting
661 def __init__ (self, how, who):
671 if self.ndx == len(self.who):
673 return ( self.how, self.who[self.ndx] )
675 return producer(how, [ "World", "PostgreSQL", "PL/Python" ])
676 $$ LANGUAGE plpythonu;
683 <term>Generator (<literal>yield</literal>)</term>
687 CREATE FUNCTION greet (how text)
688 RETURNS SETOF greeting
690 for who in [ "World", "PostgreSQL", "PL/Python" ]:
692 $$ LANGUAGE plpythonu;
698 <ulink url="http://bugs.python.org/issue1483133">bug #1483133</ulink>,
699 some debug versions of Python 2.4
700 (configured and compiled with option <literal>--with-pydebug</literal>)
701 are known to crash the <productname>PostgreSQL</productname> server
702 when using an iterator to return a set result.
703 Unpatched versions of Fedora 4 contain this bug.
704 It does not happen in production versions of Python or on patched
705 versions of Fedora 4.
715 Set-returning functions with <literal>OUT</literal> parameters
716 (using <literal>RETURNS SETOF record</literal>) are also
717 supported. For example:
719 CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer) RETURNS SETOF record AS $$
721 $$ LANGUAGE plpythonu;
723 SELECT * FROM multiout_simple_setof(3);
729 <sect1 id="plpython-sharing">
730 <title>Sharing Data</title>
732 The global dictionary <varname>SD</varname> is available to store
733 data between function calls. This variable is private static data.
734 The global dictionary <varname>GD</varname> is public data,
735 available to all Python functions within a session. Use with
736 care.<indexterm><primary>global data</>
737 <secondary>in PL/Python</></indexterm>
741 Each function gets its own execution environment in the
742 Python interpreter, so that global data and function arguments from
743 <function>myfunc</function> are not available to
744 <function>myfunc2</function>. The exception is the data in the
745 <varname>GD</varname> dictionary, as mentioned above.
749 <sect1 id="plpython-do">
750 <title>Anonymous Code Blocks</title>
753 PL/Python also supports anonymous code blocks called with the
754 <xref linkend="sql-do"> statement:
759 $$ LANGUAGE plpythonu;
762 An anonymous code block receives no arguments, and whatever value it
763 might return is discarded. Otherwise it behaves just like a function.
767 <sect1 id="plpython-trigger">
768 <title>Trigger Functions</title>
770 <indexterm zone="plpython-trigger">
771 <primary>trigger</primary>
772 <secondary>in PL/Python</secondary>
776 When a function is used as a trigger, the dictionary
777 <literal>TD</literal> contains trigger-related values:
780 <term><literal>TD["event"]</></term>
783 contains the event as a string:
784 <literal>INSERT</>, <literal>UPDATE</>,
785 <literal>DELETE</>, or <literal>TRUNCATE</>.
791 <term><literal>TD["when"]</></term>
794 contains one of <literal>BEFORE</>, <literal>AFTER</>, or
795 <literal>INSTEAD OF</>.
801 <term><literal>TD["level"]</></term>
804 contains <literal>ROW</> or <literal>STATEMENT</>.
810 <term><literal>TD["new"]</></term>
811 <term><literal>TD["old"]</></term>
814 For a row-level trigger, one or both of these fields contain
815 the respective trigger rows, depending on the trigger event.
821 <term><literal>TD["name"]</></term>
824 contains the trigger name.
830 <term><literal>TD["table_name"]</></term>
833 contains the name of the table on which the trigger occurred.
839 <term><literal>TD["table_schema"]</></term>
842 contains the schema of the table on which the trigger occurred.
848 <term><literal>TD["relid"]</></term>
851 contains the OID of the table on which the trigger occurred.
857 <term><literal>TD["args"]</></term>
860 If the <command>CREATE TRIGGER</> command
861 included arguments, they are available in <literal>TD["args"][0]</> to
862 <literal>TD["args"][<replaceable>n</>-1]</>.
870 If <literal>TD["when"]</literal> is <literal>BEFORE</> or
871 <literal>INSTEAD OF</> and
872 <literal>TD["level"]</literal> is <literal>ROW</>, you can
873 return <literal>None</literal> or <literal>"OK"</literal> from the
874 Python function to indicate the row is unmodified,
875 <literal>"SKIP"</> to abort the event, or if <literal>TD["event"]</>
876 is <command>INSERT</> or <command>UPDATE</> you can return
877 <literal>"MODIFY"</> to indicate you've modified the new row.
878 Otherwise the return value is ignored.
882 <sect1 id="plpython-database">
883 <title>Database Access</title>
886 The PL/Python language module automatically imports a Python module
887 called <literal>plpy</literal>. The functions and constants in
888 this module are available to you in the Python code as
889 <literal>plpy.<replaceable>foo</replaceable></literal>.
893 <title>Database Access Functions</title>
896 The <literal>plpy</literal> module provides several functions to execute
902 <term><literal>plpy.<function>execute</function>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
905 Calling <function>plpy.execute</function> with a query string and an
906 optional row limit argument causes that query to be run and the result to
907 be returned in a result object.
911 The result object emulates a list or dictionary object. The result
912 object can be accessed by row number and column name. For example:
914 rv = plpy.execute("SELECT * FROM my_table", 5)
916 returns up to 5 rows from <literal>my_table</literal>. If
917 <literal>my_table</literal> has a column
918 <literal>my_column</literal>, it would be accessed as:
920 foo = rv[i]["my_column"]
922 The number of rows returned can be obtained using the built-in
923 <function>len</function> function.
927 The result object has these additional methods:
930 <term><literal><function>nrows</function>()</literal></term>
933 Returns the number of rows processed by the command. Note that this
934 is not necessarily the same as the number of rows returned. For
935 example, an <command>UPDATE</command> command will set this value but
936 won't return any rows (unless <literal>RETURNING</literal> is used).
942 <term><literal><function>status</function>()</literal></term>
945 The <function>SPI_execute()</function> return value.
951 <term><literal><function>colnames</function>()</literal></term>
952 <term><literal><function>coltypes</function>()</literal></term>
953 <term><literal><function>coltypmods</function>()</literal></term>
956 Return a list of column names, list of column type OIDs, and list of
957 type-specific type modifiers for the columns, respectively.
961 These methods raise an exception when called on a result object from
962 a command that did not produce a result set, e.g.,
963 <command>UPDATE</command> without <literal>RETURNING</literal>, or
964 <command>DROP TABLE</command>. But it is OK to use these methods on
965 a result set containing zero rows.
971 <term><literal><function>__str__</function>()</literal></term>
974 The standard <literal>__str__</literal> method is defined so that it
975 is possible for example to debug query execution results
976 using <literal>plpy.debug(rv)</literal>.
984 The result object can be modified.
988 Note that calling <literal>plpy.execute</literal> will cause the entire
989 result set to be read into memory. Only use that function when you are
990 sure that the result set will be relatively small. If you don't want to
991 risk excessive memory usage when fetching large results,
992 use <literal>plpy.cursor</literal> rather
993 than <literal>plpy.execute</literal>.
999 <term><literal>plpy.<function>prepare</function>(<replaceable>query</replaceable> [, <replaceable>argtypes</replaceable>])</literal></term>
1000 <term><literal>plpy.<function>execute</function>(<replaceable>plan</replaceable> [, <replaceable>arguments</replaceable> [, <replaceable>max-rows</replaceable>]])</literal></term>
1003 <indexterm><primary>preparing a query</><secondary>in PL/Python</></indexterm>
1004 <function>plpy.prepare</function> prepares the execution plan for a
1005 query. It is called with a query string and a list of parameter types,
1006 if you have parameter references in the query. For example:
1008 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])
1010 <literal>text</literal> is the type of the variable you will be passing
1011 for <literal>$1</literal>. The second argument is optional if you don't
1012 want to pass any parameters to the query.
1015 After preparing a statement, you use a variant of the
1016 function <function>plpy.execute</function> to run it:
1018 rv = plpy.execute(plan, ["name"], 5)
1020 Pass the plan as the first argument (instead of the query string), and a
1021 list of values to substitute into the query as the second argument. The
1022 second argument is optional if the query does not expect any parameters.
1023 The third argument is the optional row limit as before.
1027 Query parameters and result row fields are converted between PostgreSQL
1028 and Python data types as described in <xref linkend="plpython-data">.
1029 The exception is that composite types are currently not supported: They
1030 will be rejected as query parameters and are converted to strings when
1031 appearing in a query result. As a workaround for the latter problem, the
1032 query can sometimes be rewritten so that the composite type result
1033 appears as a result row rather than as a field of the result row.
1034 Alternatively, the resulting string could be parsed apart by hand, but
1035 this approach is not recommended because it is not future-proof.
1039 When you prepare a plan using the PL/Python module it is automatically
1040 saved. Read the SPI documentation (<xref linkend="spi">) for a
1041 description of what this means. In order to make effective use of this
1042 across function calls one needs to use one of the persistent storage
1043 dictionaries <literal>SD</literal> or <literal>GD</literal> (see
1044 <xref linkend="plpython-sharing">). For example:
1046 CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
1047 plan = SD.setdefault("plan", plpy.prepare("SELECT 1"))
1049 $$ LANGUAGE plpythonu;
1056 <term><literal>plpy.<function>cursor</function>(<replaceable>query</replaceable>)</literal></term>
1057 <term><literal>plpy.<function>cursor</function>(<replaceable>plan</replaceable> [, <replaceable>arguments</replaceable>])</literal></term>
1060 The <literal>plpy.cursor</literal> function accepts the same arguments
1061 as <literal>plpy.execute</literal> (except for the row limit) and returns
1062 a cursor object, which allows you to process large result sets in smaller
1063 chunks. As with <literal>plpy.execute</literal>, either a query string
1064 or a plan object along with a list of arguments can be used.
1068 The cursor object provides a <literal>fetch</literal> method that accepts
1069 an integer parameter and returns a result object. Each time you
1070 call <literal>fetch</literal>, the returned object will contain the next
1071 batch of rows, never larger than the parameter value. Once all rows are
1072 exhausted, <literal>fetch</literal> starts returning an empty result
1073 object. Cursor objects also provide an
1074 <ulink url="http://docs.python.org/library/stdtypes.html#iterator-types">iterator
1075 interface</ulink>, yielding one row at a time until all rows are
1076 exhausted. Data fetched that way is not returned as result objects, but
1077 rather as dictionaries, each dictionary corresponding to a single result
1082 An example of two ways of processing data from a large table is:
1084 CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
1086 for row in plpy.cursor("select num from largetable"):
1090 $$ LANGUAGE plpythonu;
1092 CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
1094 cursor = plpy.cursor("select num from largetable")
1096 rows = cursor.fetch(batch_size)
1103 $$ LANGUAGE plpythonu;
1105 CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
1107 plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])
1108 rows = list(plpy.cursor(plan, [2]))
1111 $$ LANGUAGE plpythonu;
1116 Cursors are automatically disposed of. But if you want to explicitly
1117 release all resources held by a cursor, use the <literal>close</literal>
1118 method. Once closed, a cursor cannot be fetched from anymore.
1123 Do not confuse objects created by <literal>plpy.cursor</literal> with
1124 DB-API cursors as defined by
1125 the <ulink url="http://www.python.org/dev/peps/pep-0249/">Python
1126 Database API specification</ulink>. They don't have anything in common
1127 except for the name.
1136 <sect2 id="plpython-trapping">
1137 <title>Trapping Errors</title>
1140 Functions accessing the database might encounter errors, which
1141 will cause them to abort and raise an exception. Both
1142 <function>plpy.execute</function> and
1143 <function>plpy.prepare</function> can raise an instance of a subclass of
1144 <literal>plpy.SPIError</literal>, which by default will terminate
1145 the function. This error can be handled just like any other
1146 Python exception, by using the <literal>try/except</literal>
1147 construct. For example:
1149 CREATE FUNCTION try_adding_joe() RETURNS text AS $$
1151 plpy.execute("INSERT INTO users(username) VALUES ('joe')")
1152 except plpy.SPIError:
1153 return "something went wrong"
1156 $$ LANGUAGE plpythonu;
1161 The actual class of the exception being raised corresponds to the
1162 specific condition that caused the error. Refer
1163 to <xref linkend="errcodes-table"> for a list of possible
1164 conditions. The module
1165 <literal>plpy.spiexceptions</literal> defines an exception class
1166 for each <productname>PostgreSQL</productname> condition, deriving
1167 their names from the condition name. For
1168 instance, <literal>division_by_zero</literal>
1169 becomes <literal>DivisionByZero</literal>, <literal>unique_violation</literal>
1170 becomes <literal>UniqueViolation</literal>, <literal>fdw_error</literal>
1171 becomes <literal>FdwError</literal>, and so on. Each of these
1172 exception classes inherits from <literal>SPIError</literal>. This
1173 separation makes it easier to handle specific errors, for
1176 CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
1177 from plpy import spiexceptions
1179 plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
1180 plpy.execute(plan, [numerator, denominator])
1181 except spiexceptions.DivisionByZero:
1182 return "denominator cannot equal zero"
1183 except spiexceptions.UniqueViolation:
1184 return "already have that fraction"
1185 except plpy.SPIError, e:
1186 return "other error, SQLSTATE %s" % e.sqlstate
1188 return "fraction inserted"
1189 $$ LANGUAGE plpythonu;
1191 Note that because all exceptions from
1192 the <literal>plpy.spiexceptions</literal> module inherit
1193 from <literal>SPIError</literal>, an <literal>except</literal>
1194 clause handling it will catch any database access error.
1198 As an alternative way of handling different error conditions, you
1199 can catch the <literal>SPIError</literal> exception and determine
1200 the specific error condition inside the <literal>except</literal>
1201 block by looking at the <literal>sqlstate</literal> attribute of
1202 the exception object. This attribute is a string value containing
1203 the <quote>SQLSTATE</quote> error code. This approach provides
1204 approximately the same functionality
1209 <sect1 id="plpython-subtransaction">
1210 <title>Explicit Subtransactions</title>
1213 Recovering from errors caused by database access as described in
1214 <xref linkend="plpython-trapping"> can lead to an undesirable
1215 situation where some operations succeed before one of them fails,
1216 and after recovering from that error the data is left in an
1217 inconsistent state. PL/Python offers a solution to this problem in
1218 the form of explicit subtransactions.
1222 <title>Subtransaction Context Managers</title>
1225 Consider a function that implements a transfer between two
1228 CREATE FUNCTION transfer_funds() RETURNS void AS $$
1230 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1231 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1232 except plpy.SPIError, e:
1233 result = "error transferring funds: %s" % e.args
1235 result = "funds transferred correctly"
1236 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1237 plpy.execute(plan, [result])
1238 $$ LANGUAGE plpythonu;
1240 If the second <literal>UPDATE</literal> statement results in an
1241 exception being raised, this function will report the error, but
1242 the result of the first <literal>UPDATE</literal> will
1243 nevertheless be committed. In other words, the funds will be
1244 withdrawn from Joe's account, but will not be transferred to
1249 To avoid such issues, you can wrap your
1250 <literal>plpy.execute</literal> calls in an explicit
1251 subtransaction. The <literal>plpy</literal> module provides a
1252 helper object to manage explicit subtransactions that gets created
1253 with the <literal>plpy.subtransaction()</literal> function.
1254 Objects created by this function implement the
1255 <ulink url="http://docs.python.org/library/stdtypes.html#context-manager-types">
1256 context manager interface</ulink>. Using explicit subtransactions
1257 we can rewrite our function as:
1259 CREATE FUNCTION transfer_funds2() RETURNS void AS $$
1261 with plpy.subtransaction():
1262 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1263 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1264 except plpy.SPIError, e:
1265 result = "error transferring funds: %s" % e.args
1267 result = "funds transferred correctly"
1268 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1269 plpy.execute(plan, [result])
1270 $$ LANGUAGE plpythonu;
1272 Note that the use of <literal>try/catch</literal> is still
1273 required. Otherwise the exception would propagate to the top of
1274 the Python stack and would cause the whole function to abort with
1275 a <productname>PostgreSQL</productname> error, so that the
1276 <literal>operations</literal> table would not have any row
1277 inserted into it. The subtransaction context manager does not
1278 trap errors, it only assures that all database operations executed
1279 inside its scope will be atomically committed or rolled back. A
1280 rollback of the subtransaction block occurs on any kind of
1281 exception exit, not only ones caused by errors originating from
1282 database access. A regular Python exception raised inside an
1283 explicit subtransaction block would also cause the subtransaction
1289 <title>Older Python Versions</title>
1292 Context managers syntax using the <literal>with</literal> keyword
1293 is available by default in Python 2.6. If using PL/Python with an
1294 older Python version, it is still possible to use explicit
1295 subtransactions, although not as transparently. You can call the
1296 subtransaction manager's <literal>__enter__</literal> and
1297 <literal>__exit__</literal> functions using the
1298 <literal>enter</literal> and <literal>exit</literal> convenience
1299 aliases. The example function that transfers funds could be
1302 CREATE FUNCTION transfer_funds_old() RETURNS void AS $$
1304 subxact = plpy.subtransaction()
1307 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1308 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1311 subxact.exit(*sys.exc_info())
1314 subxact.exit(None, None, None)
1315 except plpy.SPIError, e:
1316 result = "error transferring funds: %s" % e.args
1318 result = "funds transferred correctly"
1320 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1321 plpy.execute(plan, [result])
1322 $$ LANGUAGE plpythonu;
1328 Although context managers were implemented in Python 2.5, to use
1329 the <literal>with</literal> syntax in that version you need to
1331 url="http://docs.python.org/release/2.5/ref/future.html">future
1332 statement</ulink>. Because of implementation details, however,
1333 you cannot use future statements in PL/Python functions.
1339 <sect1 id="plpython-util">
1340 <title>Utility Functions</title>
1342 The <literal>plpy</literal> module also provides the functions
1343 <literal>plpy.debug(<replaceable>msg</>)</literal>,
1344 <literal>plpy.log(<replaceable>msg</>)</literal>,
1345 <literal>plpy.info(<replaceable>msg</>)</literal>,
1346 <literal>plpy.notice(<replaceable>msg</>)</literal>,
1347 <literal>plpy.warning(<replaceable>msg</>)</literal>,
1348 <literal>plpy.error(<replaceable>msg</>)</literal>, and
1349 <literal>plpy.fatal(<replaceable>msg</>)</literal>.<indexterm><primary>elog</><secondary>in PL/Python</></indexterm>
1350 <function>plpy.error</function> and
1351 <function>plpy.fatal</function> actually raise a Python exception
1352 which, if uncaught, propagates out to the calling query, causing
1353 the current transaction or subtransaction to be aborted.
1354 <literal>raise plpy.Error(<replaceable>msg</>)</literal> and
1355 <literal>raise plpy.Fatal(<replaceable>msg</>)</literal> are
1356 equivalent to calling
1357 <function>plpy.error</function> and
1358 <function>plpy.fatal</function>, respectively.
1359 The other functions only generate messages of different
1361 Whether messages of a particular priority are reported to the client,
1362 written to the server log, or both is controlled by the
1363 <xref linkend="guc-log-min-messages"> and
1364 <xref linkend="guc-client-min-messages"> configuration
1365 variables. See <xref linkend="runtime-config"> for more information.
1369 Another set of utility functions are
1370 <literal>plpy.quote_literal(<replaceable>string</>)</literal>,
1371 <literal>plpy.quote_nullable(<replaceable>string</>)</literal>, and
1372 <literal>plpy.quote_ident(<replaceable>string</>)</literal>. They
1373 are equivalent to the built-in quoting functions described in <xref
1374 linkend="functions-string">. They are useful when constructing
1375 ad-hoc queries. A PL/Python equivalent of dynamic SQL from <xref
1376 linkend="plpgsql-quote-literal-example"> would be:
1378 plpy.execute("UPDATE tbl SET %s = %s WHERE key = %s" % (
1379 plpy.quote_ident(colname),
1380 plpy.quote_nullable(newvalue),
1381 plpy.quote_literal(keyvalue)))
1386 <sect1 id="plpython-envar">
1387 <title>Environment Variables</title>
1390 Some of the environment variables that are accepted by the Python
1391 interpreter can also be used to affect PL/Python behavior. They
1392 would need to be set in the environment of the main PostgreSQL
1393 server process, for example in a start script. The available
1394 environment variables depend on the version of Python; see the
1395 Python documentation for details. At the time of this writing, the
1396 following environment variables have an affect on PL/Python,
1397 assuming an adequate Python version:
1400 <para><envar>PYTHONHOME</envar></para>
1404 <para><envar>PYTHONPATH</envar></para>
1408 <para><envar>PYTHONY2K</envar></para>
1412 <para><envar>PYTHONOPTIMIZE</envar></para>
1416 <para><envar>PYTHONDEBUG</envar></para>
1420 <para><envar>PYTHONVERBOSE</envar></para>
1424 <para><envar>PYTHONCASEOK</envar></para>
1428 <para><envar>PYTHONDONTWRITEBYTECODE</envar></para>
1432 <para><envar>PYTHONIOENCODING</envar></para>
1436 <para><envar>PYTHONUSERBASE</envar></para>
1440 <para><envar>PYTHONHASHSEED</envar></para>
1444 (It appears to be a Python implementation detail beyond the control
1445 of PL/Python that some of the environment variables listed on
1446 the <command>python</command> man page are only effective in a
1447 command-line interpreter and not an embedded Python interpreter.)