1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/plpython.sgml,v 1.8 2002/01/07 02:29:13 petere Exp $ -->
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</></>
9 <sect1 id="plpython-intro">
10 <title>Introduction</title>
13 The <application>PL/Python</application> procedural language allows
14 <productname>PostgreSQL</productname> functions to be written in
15 the <ulink url="http://www.python.org">Python</ulink> language.
19 The current version of <application>PL/Python</application>
20 functions as a trusted language only; access to the file system and
21 other local resources is disabled. Specifically,
22 <application>PL/Python</application> uses the Python restricted
23 execution environment, further restricts it to prevent the use of
24 the file <function>open</> call, and allows only modules from a
25 specific list to be imported. Presently, that list includes:
26 array, bisect, binascii, calendar, cmath, codecs, errno, marshal,
27 math, md5, mpz, operator, pcre, pickle, random, re, regex, sre,
28 sha, string, StringIO, struct, time, whrandom, and zlib.
32 In the current version, any database error encountered while
33 running a <application>PL/Python</application> function will result
34 in the immediate termination of that function by the server. It is
35 not possible to trap error conditions using Python <literal>try
36 ... catch</literal> constructs. For example, a syntax error in an
37 SQL statement passed to the <literal>plpy.execute()</literal> call
38 will terminate the function. This behavior may be changed in a
43 <sect1 id="plpython-install">
44 <title>Installation</title>
47 To build PL/Python, the <option>--with-python</option> option needs
48 to be specified when running <filename>configure</filename>. If
49 after building and installing you have a file called
50 <filename>plpython.so</filename> (possibly a different extension),
51 then everything went well. Otherwise you should have seen a notice
54 *** Cannot build PL/Python because libpython is not a shared library.
55 *** You might have to rebuild your Python installation. Refer to
56 *** the documentation for details.
58 That means you have to rebuild (part of) your Python installation
59 to supply this shared library.
63 The catch is that the Python distribution or the Python maintainers
64 do not provide any direct way to do this. The closest thing we can
65 offer you is the information in <ulink
66 url="http://www.python.org/doc/FAQ.html#3.30">Python FAQ
67 3.30</ulink>. On some operating systems you don't really have to
68 build a shared library, but then you will have to convince the
69 PostgreSQL build system of this. Consult the
70 <filename>Makefile</filename> in the
71 <filename>src/pl/plpython</filename> directory for details.
75 <sect1 id="plpython-using">
76 <title>Using PL/Python</title>
79 There are sample functions in
80 <filename>plpython_function.sql</filename>. The Python code you
81 write gets transformed into a function. E.g.,
83 CREATE FUNCTION myfunc(text) RETURNS text AS
91 def __plpython_procedure_myfunc_23456():
95 where 23456 is the Oid of the function.
99 If you do not provide a return value, Python returns the default
100 <symbol>None</symbol> which may or may not be what you want. The
101 language module translates Python's None into SQL NULL.
105 <productname>PostgreSQL</> function variables are available in the global
106 <varname>args</varname> list. In the <function>myfunc</function>
107 example, <varname>args[0]</> contains whatever was passed in as the text
108 argument. For <literal>myfunc2(text, integer)</literal>, <varname>args[0]</>
109 would contain the <type>text</type> variable and <varname>args[1]</varname> the <type>integer</type> variable.
113 The global dictionary SD is available to store data between
114 function calls. This variable is private static data. The global
115 dictionary GD is public data, available to all python functions
116 within a backend. Use with care.
120 Each function gets its own restricted execution object in the
121 Python interpreter, so that global data and function arguments from
122 <function>myfunc</function> are not available to
123 <function>myfunc2</function>. The exception is the data in the GD
124 dictionary, as mentioned above.
128 When a function is used in a trigger, the dictionary TD contains
129 transaction related values. The trigger tuples are in <literal>TD["new"]</>
130 and/or <literal>TD["old"]</> depending on the trigger event. <literal>TD["event"]</>
131 contains the event as a string (<literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or
132 <literal>UNKNOWN</>). TD["when"] contains one of (<literal>BEFORE</>, <literal>AFTER</>, or
133 <literal>UNKNOWN</>). <literal>TD["level"]</> contains one of <literal>ROW</>, <literal>STATEMENT</>, or
134 <literal>UNKNOWN</>. <literal>TD["name"]</> contains the trigger name, and <literal>TD["relid"]</>
135 contains the relation id of the table on which the trigger occurred.
136 If the trigger was called with arguments they are available
137 in <literal>TD["args"][0]</> to <literal>TD["args"][(n -1)]</>.
141 If the trigger <quote>when</quote> is <literal>BEFORE</>, you may return <literal>None</literal> or <literal>"OK"</literal>
142 from the Python function to indicate the tuple is unmodified,
143 <literal>"SKIP"</> to abort the event, or <literal>"MODIFIED"</> to indicate you've
148 The PL/Python language module automatically imports a Python module
149 called <literal>plpy</literal>. The functions and constants in
150 this module are available to you in the Python code as
151 <literal>plpy.<replaceable>foo</replaceable></literal>. At present
152 <literal>plpy</literal> implements the functions
153 <literal>plpy.error("msg")</literal>,
154 <literal>plpy.fatal("msg")</literal>,
155 <literal>plpy.debug("msg")</literal>, and
156 <literal>plpy.notice("msg")</literal>. They are mostly equivalent
157 to calling <literal>elog(<replaceable>LEVEL</>, "msg")</literal>,
158 where <replaceable>LEVEL</> is DEBUG, ERROR, FATAL or NOTICE.
159 <function>plpy.error</function> and <function>plpy.fatal</function>
160 actually raise a Python exception which, if uncaught, causes the
161 PL/Python module to call <literal>elog(ERROR, msg)</literal> when
162 the function handler returns from the Python interpreter. Long
163 jumping out of the Python interpreter is probably not good.
164 <literal>raise plpy.ERROR("msg")</literal> and <literal>raise
165 plpy.FATAL("msg")</literal> are equivalent to calling
166 <function>plpy.error</function> or <function>plpy.fatal</function>.
170 Additionally, the <literal>plpy</literal> module provides two functions called
171 <function>execute</function> and <function>prepare</function>.
172 Calling <function>plpy.execute</function> with a query string, and
173 an optional limit argument, causes that query to be run, and the
174 result returned in a result object. The result object emulates a
175 list or dictionary object. The result object can be accessed by
176 row number, and field name. It has these additional methods:
177 <function>nrows()</function> which returns the number of rows
178 returned by the query, and <function>status</function> which is the
179 <function>SPI_exec</function> return variable. The result object
183 rv = plpy.execute("SELECT * FROM my_table", 5)
185 returns up to 5 rows from my_table. Ff my_table has a column
186 my_field it would be accessed as
188 foo = rv[i]["my_field"]
190 The second function <function>plpy.prepare</function> is called
191 with a query string, and a list of argument types if you have bind
192 variables in the query.
194 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])
196 text is the type of the variable you will be passing as $1. After
197 preparing you use the function <function>plpy.execute</function> to
200 rv = plpy.execute(plan, [ "name" ], 5)
202 The limit argument is optional in the call to
203 <function>plpy.execute</function>.
207 When you prepare a plan using the PL/Python module it is
208 automatically saved. Read the SPI documentation (<xref
209 linkend="spi">) for a description of what this means. The take
210 home message is if you do
212 plan = plpy.prepare("SOME QUERY")
213 plan = plpy.prepare("SOME OTHER QUERY")
215 you are leaking memory, as I know of no way to free a saved plan.
216 The alternative of using unsaved plans it even more painful (for