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 As of <productname>PostgreSQL</productname> 7.4, PL/Python is only
31 available as an <quote>untrusted</> language, meaning it does not
32 offer any way of restricting what users can do in it. It has
33 therefore been renamed to <literal>plpythonu</>. The trusted
34 variant <literal>plpython</> might become available again in future,
35 if a new secure execution mechanism is developed in Python. The
36 writer of a function in untrusted PL/Python must take care that the
37 function cannot be used to do anything unwanted, since it will be
38 able to do anything that could be done by a user logged in as the
39 database administrator. Only superusers can create functions in
40 untrusted languages such as <literal>plpythonu</literal>.
45 Users of source packages must specially enable the build of
46 PL/Python during the installation process. (Refer to the
47 installation instructions for more information.) Users of binary
48 packages might find PL/Python in a separate subpackage.
52 <sect1 id="plpython-python23">
53 <title>Python 2 vs. Python 3</title>
56 PL/Python supports both the Python 2 and Python 3 language
57 variants. (The PostgreSQL installation instructions might contain
58 more precise information about the exact supported minor versions
59 of Python.) Because the Python 2 and Python 3 language variants
60 are incompatible in some important aspects, the following naming
61 and transitioning scheme is used by PL/Python to avoid mixing them:
66 The PostgreSQL language named <literal>plpython2u</literal>
67 implements PL/Python based on the Python 2 language variant.
73 The PostgreSQL language named <literal>plpython3u</literal>
74 implements PL/Python based on the Python 3 language variant.
80 The language named <literal>plpythonu</literal> implements
81 PL/Python based on the default Python language variant, which is
82 currently Python 2. (This default is independent of what any
83 local Python installations might consider to be
84 their <quote>default</quote>, for example,
85 what <filename>/usr/bin/python</filename> might be.) The
86 default will probably be changed to Python 3 in a distant future
87 release of PostgreSQL, depending on the progress of the
88 migration to Python 3 in the Python community.
93 It depends on the build configuration or the installed packages
94 whether PL/Python for Python 2 or Python 3 or both are available.
99 The built variant depends on which Python version was found during
100 the installation or which version was explicitly set using
101 the <envar>PYTHON</envar> environment variable;
102 see <xref linkend="install-procedure">. To make both variants of
103 PL/Python available in one installation, the source tree has to be
104 configured and built twice.
109 This results in the following usage and migration strategy:
114 Existing users and users who are currently not interested in
115 Python 3 use the language name <literal>plpythonu</literal> and
116 don't have to change anything for the foreseeable future. It is
117 recommended to gradually <quote>future-proof</quote> the code
118 via migration to Python 2.6/2.7 to simplify the eventual
119 migration to Python 3.
123 In practice, many PL/Python functions will migrate to Python 3
124 with few or no changes.
130 Users who know that they have heavily Python 2 dependent code
131 and don't plan to ever change it can make use of
132 the <literal>plpython2u</literal> language name. This will
133 continue to work into the very distant future, until Python 2
134 support might be completely dropped by PostgreSQL.
140 Users who want to dive into Python 3 can use
141 the <literal>plpython3u</literal> language name, which will keep
142 working forever by today's standards. In the distant future,
143 when Python 3 might become the default, they might like to
144 remove the <quote>3</quote> for aesthetic reasons.
150 Daredevils, who want to build a Python-3-only operating system
151 environment, can change the contents of
152 <link linkend="catalog-pg-pltemplate"><structname>pg_pltemplate</structname></link>
153 to make <literal>plpythonu</literal> be equivalent
154 to <literal>plpython3u</literal>, keeping in mind that this
155 would make their installation incompatible with most of the rest
164 document <ulink url="http://docs.python.org/py3k/whatsnew/3.0.html">What's
165 New In Python 3.0</ulink> for more information about porting to
170 It is not allowed to use PL/Python based on Python 2 and PL/Python
171 based on Python 3 in the same session, because the symbols in the
172 dynamic modules would clash, which could result in crashes of the
173 PostgreSQL server process. There is a check that prevents mixing
174 Python major versions in a session, which will abort the session if
175 a mismatch is detected. It is possible, however, to use both
176 PL/Python variants in the same database, from separate sessions.
180 <sect1 id="plpython-funcs">
181 <title>PL/Python Functions</title>
184 Functions in PL/Python are declared via the
185 standard <xref linkend="sql-createfunction"> syntax:
188 CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-list</replaceable>)
189 RETURNS <replaceable>return-type</replaceable>
191 # PL/Python function body
192 $$ LANGUAGE plpythonu;
197 The body of a function is simply a Python script. When the function
198 is called, its arguments are passed as elements of the list
199 <varname>args</varname>; named arguments are also passed as
200 ordinary variables to the Python script. Use of named arguments is
201 usually more readable. The result is returned from the Python code
202 in the usual way, with <literal>return</literal> or
203 <literal>yield</literal> (in case of a result-set statement). If
204 you do not provide a return value, Python returns the default
205 <symbol>None</symbol>. <application>PL/Python</application> translates
206 Python's <symbol>None</symbol> into the SQL null value.
210 For example, a function to return the greater of two integers can be
214 CREATE FUNCTION pymax (a integer, b integer)
220 $$ LANGUAGE plpythonu;
223 The Python code that is given as the body of the function definition
224 is transformed into a Python function. For example, the above results in:
227 def __plpython_procedure_pymax_23456():
233 assuming that 23456 is the OID assigned to the function by
234 <productname>PostgreSQL</productname>.
238 The arguments are set as global variables. Because of the scoping
239 rules of Python, this has the subtle consequence that an argument
240 variable cannot be reassigned inside the function to the value of
241 an expression that involves the variable name itself, unless the
242 variable is redeclared as global in the block. For example, the
243 following won't work:
245 CREATE FUNCTION pystrip(x text)
248 x = x.strip() # error
250 $$ LANGUAGE plpythonu;
252 because assigning to <varname>x</varname>
253 makes <varname>x</varname> a local variable for the entire block,
254 and so the <varname>x</varname> on the right-hand side of the
255 assignment refers to a not-yet-assigned local
256 variable <varname>x</varname>, not the PL/Python function
257 parameter. Using the <literal>global</literal> statement, this can
260 CREATE FUNCTION pystrip(x text)
264 x = x.strip() # ok now
266 $$ LANGUAGE plpythonu;
268 But it is advisable not to rely on this implementation detail of
269 PL/Python. It is better to treat the function parameters as
274 <sect1 id="plpython-data">
275 <title>Data Values</title>
277 Generally speaking, the aim of PL/Python is to provide
278 a <quote>natural</quote> mapping between the PostgreSQL and the
279 Python worlds. This informs the data mapping rules described
284 <title>Data Type Mapping</title>
286 Function arguments are converted from their PostgreSQL type to a
287 corresponding Python type:
291 PostgreSQL <type>boolean</type> is converted to Python <type>bool</type>.
297 PostgreSQL <type>smallint</type> and <type>int</type> are
298 converted to Python <type>int</type>.
299 PostgreSQL <type>bigint</type> is converted
300 to <type>long</type> in Python 2 and to <type>int</type> in
307 PostgreSQL <type>real</type>, <type>double</type>,
308 and <type>numeric</type> are converted to
309 Python <type>float</type>. Note that for
310 the <type>numeric</type> this loses information and can lead to
311 incorrect results. This might be fixed in a future
318 PostgreSQL <type>bytea</type> is converted to
319 Python <type>str</type> in Python 2 and to <type>bytes</type>
320 in Python 3. In Python 2, the string should be treated as a
321 byte sequence without any character encoding.
327 All other data types, including the PostgreSQL character string
328 types, are converted to a Python <type>str</type>. In Python
329 2, this string will be in the PostgreSQL server encoding; in
330 Python 3, it will be a Unicode string like all strings.
336 For nonscalar data types, see below.
343 Function return values are converted to the declared PostgreSQL
344 return data type as follows:
348 When the PostgreSQL return type is <type>boolean</type>, the
349 return value will be evaluated for truth according to the
350 <emphasis>Python</emphasis> rules. That is, 0 and empty string
351 are false, but notably <literal>'f'</literal> is true.
357 When the PostgreSQL return type is <type>bytea</type>, the
358 return value will be converted to a string (Python 2) or bytes
359 (Python 3) using the respective Python built-ins, with the
360 result being converted <type>bytea</type>.
366 For all other PostgreSQL return types, the returned Python
367 value is converted to a string using the Python
368 built-in <literal>str</literal>, and the result is passed to the
369 input function of the PostgreSQL data type.
373 Strings in Python 2 are required to be in the PostgreSQL server
374 encoding when they are passed to PostgreSQL. Strings that are
375 not valid in the current server encoding will raise an error,
376 but not all encoding mismatches can be detected, so garbage
377 data can still result when this is not done correctly. Unicode
378 strings are converted to the correct encoding automatically, so
379 it can be safer and more convenient to use those. In Python 3,
380 all strings are Unicode strings.
386 For nonscalar data types, see below.
391 Note that logical mismatches between the declared PostgreSQL
392 return type and the Python data type of the actual return object
393 are not flagged; the value will be converted in any case.
398 <title>Null, None</title>
400 If an SQL null value<indexterm><primary>null value</primary><secondary
401 sortas="PL/Python">in PL/Python</secondary></indexterm> is passed to a
402 function, the argument value will appear as <symbol>None</symbol> in
403 Python. For example, the function definition of <function>pymax</function>
404 shown in <xref linkend="plpython-funcs"> will return the wrong answer for null
405 inputs. We could add <literal>STRICT</literal> to the function definition
406 to make <productname>PostgreSQL</productname> do something more reasonable:
407 if a null value is passed, the function will not be called at all,
408 but will just return a null result automatically. Alternatively,
409 we could check for null inputs in the function body:
412 CREATE FUNCTION pymax (a integer, b integer)
415 if (a is None) or (b is None):
420 $$ LANGUAGE plpythonu;
423 As shown above, to return an SQL null value from a PL/Python
424 function, return the value <symbol>None</symbol>. This can be done whether the
425 function is strict or not.
429 <sect2 id="plpython-arrays">
430 <title>Arrays, Lists</title>
432 SQL array values are passed into PL/Python as a Python list. To
433 return an SQL array value out of a PL/Python function, return a
434 Python sequence, for example a list or tuple:
437 CREATE FUNCTION return_arr()
440 return (1, 2, 3, 4, 5)
441 $$ LANGUAGE plpythonu;
450 Note that in Python, strings are sequences, which can have
451 undesirable effects that might be familiar to Python programmers:
454 CREATE FUNCTION return_str_arr()
458 $$ LANGUAGE plpythonu;
460 SELECT return_str_arr();
470 <title>Composite Types</title>
472 Composite-type arguments are passed to the function as Python mappings. The
473 element names of the mapping are the attribute names of the composite type.
474 If an attribute in the passed row has the null value, it has the value
475 <symbol>None</symbol> in the mapping. Here is an example:
478 CREATE TABLE employee (
484 CREATE FUNCTION overpaid (e employee)
487 if e["salary"] > 200000:
489 if (e["age"] < 30) and (e["salary"] > 100000):
492 $$ LANGUAGE plpythonu;
497 There are multiple ways to return row or composite types from a Python
498 function. The following examples assume we have:
501 CREATE TYPE named_value AS (
507 A composite result can be returned as a:
511 <term>Sequence type (a tuple or list, but not a set because
512 it is not indexable)</term>
515 Returned sequence objects must have the same number of items as the
516 composite result type has fields. The item with index 0 is assigned to
517 the first field of the composite type, 1 to the second and so on. For
521 CREATE FUNCTION make_pair (name text, value integer)
524 return [ name, value ]
525 # or alternatively, as tuple: return ( name, value )
526 $$ LANGUAGE plpythonu;
529 To return a SQL null for any column, insert <symbol>None</symbol> at
530 the corresponding position.
536 <term>Mapping (dictionary)</term>
539 The value for each result type column is retrieved from the mapping
540 with the column name as key. Example:
543 CREATE FUNCTION make_pair (name text, value integer)
546 return { "name": name, "value": value }
547 $$ LANGUAGE plpythonu;
550 Any extra dictionary key/value pairs are ignored. Missing keys are
552 To return a SQL null value for any column, insert
553 <symbol>None</symbol> with the corresponding column name as the key.
559 <term>Object (any object providing method <literal>__getattr__</literal>)</term>
562 This works the same as a mapping.
566 CREATE FUNCTION make_pair (name text, value integer)
570 def __init__ (self, n, v):
573 return named_value(name, value)
580 $$ LANGUAGE plpythonu;
589 Functions with <literal>OUT</literal> parameters are also supported. For example:
591 CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$
593 $$ LANGUAGE plpythonu;
595 SELECT * FROM multiout_simple();
601 <title>Set-returning Functions</title>
603 A <application>PL/Python</application> function can also return sets of
604 scalar or composite types. There are several ways to achieve this because
605 the returned object is internally turned into an iterator. The following
606 examples assume we have composite type:
609 CREATE TYPE greeting AS (
615 A set result can be returned from a:
619 <term>Sequence type (tuple, list, set)</term>
623 CREATE FUNCTION greet (how text)
624 RETURNS SETOF greeting
626 # return tuple containing lists as composite types
627 # all other combinations work also
628 return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
629 $$ LANGUAGE plpythonu;
636 <term>Iterator (any object providing <symbol>__iter__</symbol> and
637 <symbol>next</symbol> methods)</term>
641 CREATE FUNCTION greet (how text)
642 RETURNS SETOF greeting
645 def __init__ (self, how, who):
655 if self.ndx == len(self.who):
657 return ( self.how, self.who[self.ndx] )
659 return producer(how, [ "World", "PostgreSQL", "PL/Python" ])
660 $$ LANGUAGE plpythonu;
667 <term>Generator (<literal>yield</literal>)</term>
671 CREATE FUNCTION greet (how text)
672 RETURNS SETOF greeting
674 for who in [ "World", "PostgreSQL", "PL/Python" ]:
676 $$ LANGUAGE plpythonu;
682 <ulink url="http://bugs.python.org/issue1483133">bug #1483133</ulink>,
683 some debug versions of Python 2.4
684 (configured and compiled with option <literal>--with-pydebug</literal>)
685 are known to crash the <productname>PostgreSQL</productname> server
686 when using an iterator to return a set result.
687 Unpatched versions of Fedora 4 contain this bug.
688 It does not happen in production versions of Python or on patched
689 versions of Fedora 4.
699 Set-returning functions with <literal>OUT</literal> parameters
700 (using <literal>RETURNS SETOF record</literal>) are also
701 supported. For example:
703 CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer) RETURNS SETOF record AS $$
705 $$ LANGUAGE plpythonu;
707 SELECT * FROM multiout_simple_setof(3);
713 <sect1 id="plpython-sharing">
714 <title>Sharing Data</title>
716 The global dictionary <varname>SD</varname> is available to store
717 data between function calls. This variable is private static data.
718 The global dictionary <varname>GD</varname> is public data,
719 available to all Python functions within a session. Use with
720 care.<indexterm><primary>global data</>
721 <secondary>in PL/Python</></indexterm>
725 Each function gets its own execution environment in the
726 Python interpreter, so that global data and function arguments from
727 <function>myfunc</function> are not available to
728 <function>myfunc2</function>. The exception is the data in the
729 <varname>GD</varname> dictionary, as mentioned above.
733 <sect1 id="plpython-do">
734 <title>Anonymous Code Blocks</title>
737 PL/Python also supports anonymous code blocks called with the
738 <xref linkend="sql-do"> statement:
743 $$ LANGUAGE plpythonu;
746 An anonymous code block receives no arguments, and whatever value it
747 might return is discarded. Otherwise it behaves just like a function.
751 <sect1 id="plpython-trigger">
752 <title>Trigger Functions</title>
754 <indexterm zone="plpython-trigger">
755 <primary>trigger</primary>
756 <secondary>in PL/Python</secondary>
760 When a function is used as a trigger, the dictionary
761 <literal>TD</literal> contains trigger-related values:
764 <term><literal>TD["event"]</></term>
767 contains the event as a string:
768 <literal>INSERT</>, <literal>UPDATE</>,
769 <literal>DELETE</>, or <literal>TRUNCATE</>.
775 <term><literal>TD["when"]</></term>
778 contains one of <literal>BEFORE</>, <literal>AFTER</>, or
779 <literal>INSTEAD OF</>.
785 <term><literal>TD["level"]</></term>
788 contains <literal>ROW</> or <literal>STATEMENT</>.
794 <term><literal>TD["new"]</></term>
795 <term><literal>TD["old"]</></term>
798 For a row-level trigger, one or both of these fields contain
799 the respective trigger rows, depending on the trigger event.
805 <term><literal>TD["name"]</></term>
808 contains the trigger name.
814 <term><literal>TD["table_name"]</></term>
817 contains the name of the table on which the trigger occurred.
823 <term><literal>TD["table_schema"]</></term>
826 contains the schema of the table on which the trigger occurred.
832 <term><literal>TD["relid"]</></term>
835 contains the OID of the table on which the trigger occurred.
841 <term><literal>TD["args"]</></term>
844 If the <command>CREATE TRIGGER</> command
845 included arguments, they are available in <literal>TD["args"][0]</> to
846 <literal>TD["args"][<replaceable>n</>-1]</>.
854 If <literal>TD["when"]</literal> is <literal>BEFORE</> or
855 <literal>INSTEAD OF</> and
856 <literal>TD["level"]</literal> is <literal>ROW</>, you can
857 return <literal>None</literal> or <literal>"OK"</literal> from the
858 Python function to indicate the row is unmodified,
859 <literal>"SKIP"</> to abort the event, or if <literal>TD["event"]</>
860 is <command>INSERT</> or <command>UPDATE</> you can return
861 <literal>"MODIFY"</> to indicate you've modified the new row.
862 Otherwise the return value is ignored.
866 <sect1 id="plpython-database">
867 <title>Database Access</title>
870 The PL/Python language module automatically imports a Python module
871 called <literal>plpy</literal>. The functions and constants in
872 this module are available to you in the Python code as
873 <literal>plpy.<replaceable>foo</replaceable></literal>.
877 <title>Database Access Functions</title>
880 The <literal>plpy</literal> module provides several functions to execute
886 <term><literal>plpy.<function>execute</function>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
889 Calling <function>plpy.execute</function> with a query string and an
890 optional row limit argument causes that query to be run and the result to
891 be returned in a result object.
895 The result object emulates a list or dictionary object. The result
896 object can be accessed by row number and column name. For example:
898 rv = plpy.execute("SELECT * FROM my_table", 5)
900 returns up to 5 rows from <literal>my_table</literal>. If
901 <literal>my_table</literal> has a column
902 <literal>my_column</literal>, it would be accessed as:
904 foo = rv[i]["my_column"]
906 The number of rows returned can be obtained using the built-in
907 <function>len</function> function.
911 The result object has these additional methods:
914 <term><literal><function>nrows</function>()</literal></term>
917 Returns the number of rows processed by the command. Note that this
918 is not necessarily the same as the number of rows returned. For
919 example, an <command>UPDATE</command> command will set this value but
920 won't return any rows (unless <literal>RETURNING</literal> is used).
926 <term><literal><function>status</function>()</literal></term>
929 The <function>SPI_execute()</function> return value.
935 <term><literal><function>colnames</function>()</literal></term>
936 <term><literal><function>coltypes</function>()</literal></term>
937 <term><literal><function>coltypmods</function>()</literal></term>
940 Return a list of column names, list of column type OIDs, and list of
941 type-specific type modifiers for the columns, respectively.
945 These methods raise an exception when called on a result object from
946 a command that did not produce a result set, e.g.,
947 <command>UPDATE</command> without <literal>RETURNING</literal>, or
948 <command>DROP TABLE</command>. But it is OK to use these methods on
949 a result set containing zero rows.
957 The result object can be modified.
961 Note that calling <literal>plpy.execute</literal> will cause the entire
962 result set to be read into memory. Only use that function when you are
963 sure that the result set will be relatively small. If you don't want to
964 risk excessive memory usage when fetching large results,
965 use <literal>plpy.cursor</literal> rather
966 than <literal>plpy.execute</literal>.
972 <term><literal>plpy.<function>prepare</function>(<replaceable>query</replaceable> [, <replaceable>argtypes</replaceable>])</literal></term>
973 <term><literal>plpy.<function>execute</function>(<replaceable>plan</replaceable> [, <replaceable>arguments</replaceable> [, <replaceable>max-rows</replaceable>]])</literal></term>
976 <indexterm><primary>preparing a query</><secondary>in PL/Python</></indexterm>
977 <function>plpy.prepare</function> prepares the execution plan for a
978 query. It is called with a query string and a list of parameter types,
979 if you have parameter references in the query. For example:
981 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"])
983 <literal>text</literal> is the type of the variable you will be passing
984 for <literal>$1</literal>. The second argument is optional if you don't
985 want to pass any parameters to the query.
988 After preparing a statement, you use a variant of the
989 function <function>plpy.execute</function> to run it:
991 rv = plpy.execute(plan, ["name"], 5)
993 Pass the plan as the first argument (instead of the query string), and a
994 list of values to substitute into the query as the second argument. The
995 second argument is optional if the query does not expect any parameters.
996 The third argument is the optional row limit as before.
1000 Query parameters and result row fields are converted between PostgreSQL
1001 and Python data types as described in <xref linkend="plpython-data">.
1002 The exception is that composite types are currently not supported: They
1003 will be rejected as query parameters and are converted to strings when
1004 appearing in a query result. As a workaround for the latter problem, the
1005 query can sometimes be rewritten so that the composite type result
1006 appears as a result row rather than as a field of the result row.
1007 Alternatively, the resulting string could be parsed apart by hand, but
1008 this approach is not recommended because it is not future-proof.
1012 When you prepare a plan using the PL/Python module it is automatically
1013 saved. Read the SPI documentation (<xref linkend="spi">) for a
1014 description of what this means. In order to make effective use of this
1015 across function calls one needs to use one of the persistent storage
1016 dictionaries <literal>SD</literal> or <literal>GD</literal> (see
1017 <xref linkend="plpython-sharing">). For example:
1019 CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
1020 plan = SD.setdefault("plan", plpy.prepare("SELECT 1"))
1022 $$ LANGUAGE plpythonu;
1029 <term><literal>plpy.<function>cursor</function>(<replaceable>query</replaceable>)</literal></term>
1030 <term><literal>plpy.<function>cursor</function>(<replaceable>plan</replaceable> [, <replaceable>arguments</replaceable>])</literal></term>
1033 The <literal>plpy.cursor</literal> function accepts the same arguments
1034 as <literal>plpy.execute</literal> (except for the row limit) and returns
1035 a cursor object, which allows you to process large result sets in smaller
1036 chunks. As with <literal>plpy.execute</literal>, either a query string
1037 or a plan object along with a list of arguments can be used.
1041 The cursor object provides a <literal>fetch</literal> method that accepts
1042 an integer parameter and returns a result object. Each time you
1043 call <literal>fetch</literal>, the returned object will contain the next
1044 batch of rows, never larger than the parameter value. Once all rows are
1045 exhausted, <literal>fetch</literal> starts returning an empty result
1046 object. Cursor objects also provide an
1047 <ulink url="http://docs.python.org/library/stdtypes.html#iterator-types">iterator
1048 interface</ulink>, yielding one row at a time until all rows are
1049 exhausted. Data fetched that way is not returned as result objects, but
1050 rather as dictionaries, each dictionary corresponding to a single result
1055 An example of two ways of processing data from a large table is:
1057 CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
1059 for row in plpy.cursor("select num from largetable"):
1063 $$ LANGUAGE plpythonu;
1065 CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
1067 cursor = plpy.cursor("select num from largetable")
1069 rows = cursor.fetch(batch_size)
1076 $$ LANGUAGE plpythonu;
1078 CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
1080 plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])
1081 rows = list(plpy.cursor(plan, [2]))
1084 $$ LANGUAGE plpythonu;
1089 Cursors are automatically disposed of. But if you want to explicitly
1090 release all resources held by a cursor, use the <literal>close</literal>
1091 method. Once closed, a cursor cannot be fetched from anymore.
1096 Do not confuse objects created by <literal>plpy.cursor</literal> with
1097 DB-API cursors as defined by
1098 the <ulink url="http://www.python.org/dev/peps/pep-0249/">Python
1099 Database API specification</ulink>. They don't have anything in common
1100 except for the name.
1109 <sect2 id="plpython-trapping">
1110 <title>Trapping Errors</title>
1113 Functions accessing the database might encounter errors, which
1114 will cause them to abort and raise an exception. Both
1115 <function>plpy.execute</function> and
1116 <function>plpy.prepare</function> can raise an instance of a subclass of
1117 <literal>plpy.SPIError</literal>, which by default will terminate
1118 the function. This error can be handled just like any other
1119 Python exception, by using the <literal>try/except</literal>
1120 construct. For example:
1122 CREATE FUNCTION try_adding_joe() RETURNS text AS $$
1124 plpy.execute("INSERT INTO users(username) VALUES ('joe')")
1125 except plpy.SPIError:
1126 return "something went wrong"
1129 $$ LANGUAGE plpythonu;
1134 The actual class of the exception being raised corresponds to the
1135 specific condition that caused the error. Refer
1136 to <xref linkend="errcodes-table"> for a list of possible
1137 conditions. The module
1138 <literal>plpy.spiexceptions</literal> defines an exception class
1139 for each <productname>PostgreSQL</productname> condition, deriving
1140 their names from the condition name. For
1141 instance, <literal>division_by_zero</literal>
1142 becomes <literal>DivisionByZero</literal>, <literal>unique_violation</literal>
1143 becomes <literal>UniqueViolation</literal>, <literal>fdw_error</literal>
1144 becomes <literal>FdwError</literal>, and so on. Each of these
1145 exception classes inherits from <literal>SPIError</literal>. This
1146 separation makes it easier to handle specific errors, for
1149 CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
1150 from plpy import spiexceptions
1152 plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
1153 plpy.execute(plan, [numerator, denominator])
1154 except spiexceptions.DivisionByZero:
1155 return "denominator cannot equal zero"
1156 except spiexceptions.UniqueViolation:
1157 return "already have that fraction"
1158 except plpy.SPIError, e:
1159 return "other error, SQLSTATE %s" % e.sqlstate
1161 return "fraction inserted"
1162 $$ LANGUAGE plpythonu;
1164 Note that because all exceptions from
1165 the <literal>plpy.spiexceptions</literal> module inherit
1166 from <literal>SPIError</literal>, an <literal>except</literal>
1167 clause handling it will catch any database access error.
1171 As an alternative way of handling different error conditions, you
1172 can catch the <literal>SPIError</literal> exception and determine
1173 the specific error condition inside the <literal>except</literal>
1174 block by looking at the <literal>sqlstate</literal> attribute of
1175 the exception object. This attribute is a string value containing
1176 the <quote>SQLSTATE</quote> error code. This approach provides
1177 approximately the same functionality
1182 <sect1 id="plpython-subtransaction">
1183 <title>Explicit Subtransactions</title>
1186 Recovering from errors caused by database access as described in
1187 <xref linkend="plpython-trapping"> can lead to an undesirable
1188 situation where some operations succeed before one of them fails,
1189 and after recovering from that error the data is left in an
1190 inconsistent state. PL/Python offers a solution to this problem in
1191 the form of explicit subtransactions.
1195 <title>Subtransaction Context Managers</title>
1198 Consider a function that implements a transfer between two
1201 CREATE FUNCTION transfer_funds() RETURNS void AS $$
1203 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1204 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1205 except plpy.SPIError, e:
1206 result = "error transferring funds: %s" % e.args
1208 result = "funds transferred correctly"
1209 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1210 plpy.execute(plan, [result])
1211 $$ LANGUAGE plpythonu;
1213 If the second <literal>UPDATE</literal> statement results in an
1214 exception being raised, this function will report the error, but
1215 the result of the first <literal>UPDATE</literal> will
1216 nevertheless be committed. In other words, the funds will be
1217 withdrawn from Joe's account, but will not be transferred to
1222 To avoid such issues, you can wrap your
1223 <literal>plpy.execute</literal> calls in an explicit
1224 subtransaction. The <literal>plpy</literal> module provides a
1225 helper object to manage explicit subtransactions that gets created
1226 with the <literal>plpy.subtransaction()</literal> function.
1227 Objects created by this function implement the
1228 <ulink url="http://docs.python.org/library/stdtypes.html#context-manager-types">
1229 context manager interface</ulink>. Using explicit subtransactions
1230 we can rewrite our function as:
1232 CREATE FUNCTION transfer_funds2() RETURNS void AS $$
1234 with plpy.subtransaction():
1235 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1236 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1237 except plpy.SPIError, e:
1238 result = "error transferring funds: %s" % e.args
1240 result = "funds transferred correctly"
1241 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1242 plpy.execute(plan, [result])
1243 $$ LANGUAGE plpythonu;
1245 Note that the use of <literal>try/catch</literal> is still
1246 required. Otherwise the exception would propagate to the top of
1247 the Python stack and would cause the whole function to abort with
1248 a <productname>PostgreSQL</productname> error, so that the
1249 <literal>operations</literal> table would not have any row
1250 inserted into it. The subtransaction context manager does not
1251 trap errors, it only assures that all database operations executed
1252 inside its scope will be atomically committed or rolled back. A
1253 rollback of the subtransaction block occurs on any kind of
1254 exception exit, not only ones caused by errors originating from
1255 database access. A regular Python exception raised inside an
1256 explicit subtransaction block would also cause the subtransaction
1262 <title>Older Python Versions</title>
1265 Context managers syntax using the <literal>with</literal> keyword
1266 is available by default in Python 2.6. If using PL/Python with an
1267 older Python version, it is still possible to use explicit
1268 subtransactions, although not as transparently. You can call the
1269 subtransaction manager's <literal>__enter__</literal> and
1270 <literal>__exit__</literal> functions using the
1271 <literal>enter</literal> and <literal>exit</literal> convenience
1272 aliases. The example function that transfers funds could be
1275 CREATE FUNCTION transfer_funds_old() RETURNS void AS $$
1277 subxact = plpy.subtransaction()
1280 plpy.execute("UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'")
1281 plpy.execute("UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'")
1284 subxact.exit(*sys.exc_info())
1287 subxact.exit(None, None, None)
1288 except plpy.SPIError, e:
1289 result = "error transferring funds: %s" % e.args
1291 result = "funds transferred correctly"
1293 plan = plpy.prepare("INSERT INTO operations (result) VALUES ($1)", ["text"])
1294 plpy.execute(plan, [result])
1295 $$ LANGUAGE plpythonu;
1301 Although context managers were implemented in Python 2.5, to use
1302 the <literal>with</literal> syntax in that version you need to
1304 url="http://docs.python.org/release/2.5/ref/future.html">future
1305 statement</ulink>. Because of implementation details, however,
1306 you cannot use future statements in PL/Python functions.
1312 <sect1 id="plpython-util">
1313 <title>Utility Functions</title>
1315 The <literal>plpy</literal> module also provides the functions
1316 <literal>plpy.debug(<replaceable>msg</>)</literal>,
1317 <literal>plpy.log(<replaceable>msg</>)</literal>,
1318 <literal>plpy.info(<replaceable>msg</>)</literal>,
1319 <literal>plpy.notice(<replaceable>msg</>)</literal>,
1320 <literal>plpy.warning(<replaceable>msg</>)</literal>,
1321 <literal>plpy.error(<replaceable>msg</>)</literal>, and
1322 <literal>plpy.fatal(<replaceable>msg</>)</literal>.<indexterm><primary>elog</><secondary>in PL/Python</></indexterm>
1323 <function>plpy.error</function> and
1324 <function>plpy.fatal</function> actually raise a Python exception
1325 which, if uncaught, propagates out to the calling query, causing
1326 the current transaction or subtransaction to be aborted.
1327 <literal>raise plpy.Error(<replaceable>msg</>)</literal> and
1328 <literal>raise plpy.Fatal(<replaceable>msg</>)</literal> are
1329 equivalent to calling
1330 <function>plpy.error</function> and
1331 <function>plpy.fatal</function>, respectively.
1332 The other functions only generate messages of different
1334 Whether messages of a particular priority are reported to the client,
1335 written to the server log, or both is controlled by the
1336 <xref linkend="guc-log-min-messages"> and
1337 <xref linkend="guc-client-min-messages"> configuration
1338 variables. See <xref linkend="runtime-config"> for more information.
1342 Another set of utility functions are
1343 <literal>plpy.quote_literal(<replaceable>string</>)</literal>,
1344 <literal>plpy.quote_nullable(<replaceable>string</>)</literal>, and
1345 <literal>plpy.quote_ident(<replaceable>string</>)</literal>. They
1346 are equivalent to the built-in quoting functions described in <xref
1347 linkend="functions-string">. They are useful when constructing
1348 ad-hoc queries. A PL/Python equivalent of dynamic SQL from <xref
1349 linkend="plpgsql-quote-literal-example"> would be:
1351 plpy.execute("UPDATE tbl SET %s = %s WHERE key = %s" % (
1352 plpy.quote_ident(colname),
1353 plpy.quote_nullable(newvalue),
1354 plpy.quote_literal(keyvalue)))
1359 <sect1 id="plpython-envar">
1360 <title>Environment Variables</title>
1363 Some of the environment variables that are accepted by the Python
1364 interpreter can also be used to affect PL/Python behavior. They
1365 would need to be set in the environment of the main PostgreSQL
1366 server process, for example in a start script. The available
1367 environment variables depend on the version of Python; see the
1368 Python documentation for details. At the time of this writing, the
1369 following environment variables have an affect on PL/Python,
1370 assuming an adequate Python version:
1373 <para><envar>PYTHONHOME</envar></para>
1377 <para><envar>PYTHONPATH</envar></para>
1381 <para><envar>PYTHONY2K</envar></para>
1385 <para><envar>PYTHONOPTIMIZE</envar></para>
1389 <para><envar>PYTHONDEBUG</envar></para>
1393 <para><envar>PYTHONVERBOSE</envar></para>
1397 <para><envar>PYTHONCASEOK</envar></para>
1401 <para><envar>PYTHONDONTWRITEBYTECODE</envar></para>
1405 <para><envar>PYTHONIOENCODING</envar></para>
1409 <para><envar>PYTHONUSERBASE</envar></para>
1413 <para><envar>PYTHONHASHSEED</envar></para>
1417 (It appears to be a Python implementation detail beyond the control
1418 of PL/Python that some of the environment variables listed on
1419 the <command>python</command> man page are only effective in a
1420 command-line interpreter and not an embedded Python interpreter.)