1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/plpython.sgml,v 1.10 2002/03/22 19:20:18 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.debug("msg")</literal>,
154 <literal>plpy.log("msg")</literal>,
155 <literal>plpy.info("msg")</literal>,
156 <literal>plpy.notice("msg")</literal>,
157 <literal>plpy.warning("msg")</literal>,
158 <literal>plpy.error("msg")</literal>, and
159 <literal>plpy.fatal("msg")</literal>. They are mostly equivalent
160 to calling <literal>elog(<replaceable>LEVEL</>, "msg")</literal>.
161 <function>plpy.error</function> and <function>plpy.fatal</function>
162 actually raise a Python exception which, if uncaught, causes the
163 PL/Python module to call <literal>elog(ERROR, msg)</literal> when
164 the function handler returns from the Python interpreter. Long
165 jumping out of the Python interpreter is probably not good.
166 <literal>raise plpy.ERROR("msg")</literal> and <literal>raise
167 plpy.FATAL("msg")</literal> are equivalent to calling
168 <function>plpy.error</function> or <function>plpy.fatal</function>.
172 Additionally, the <literal>plpy</literal> module provides two functions called
173 <function>execute</function> and <function>prepare</function>.
174 Calling <function>plpy.execute</function> with a query string, and
175 an optional limit argument, causes that query to be run, and the
176 result returned in a result object. The result object emulates a
177 list or dictionary object. The result object can be accessed by
178 row number, and field name. It has these additional methods:
179 <function>nrows()</function> which returns the number of rows
180 returned by the query, and <function>status</function> which is the
181 <function>SPI_exec</function> return variable. The result object
185 rv = plpy.execute("SELECT * FROM my_table", 5)
187 returns up to 5 rows from my_table. Ff my_table has a column
188 my_field it would be accessed as
190 foo = rv[i]["my_field"]
192 The second function <function>plpy.prepare</function> is called
193 with a query string, and a list of argument types if you have bind
194 variables in the query.
196 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])
198 text is the type of the variable you will be passing as $1. After
199 preparing you use the function <function>plpy.execute</function> to
202 rv = plpy.execute(plan, [ "name" ], 5)
204 The limit argument is optional in the call to
205 <function>plpy.execute</function>.
209 When you prepare a plan using the PL/Python module it is
210 automatically saved. Read the SPI documentation (<xref
211 linkend="spi">) for a description of what this means. The take
212 home message is if you do
214 plan = plpy.prepare("SOME QUERY")
215 plan = plpy.prepare("SOME OTHER QUERY")
217 you are leaking memory, as I know of no way to free a saved plan.
218 The alternative of using unsaved plans it even more painful (for