1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/plpython.sgml,v 1.21 2003/11/12 22:47:47 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</></>
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</ulink> language.
16 To install PL/Python in a particular database, use
17 <literal>createlang plpythonu <replaceable>dbname</></literal>.
22 If a language is installed into <literal>template1</>, all subsequently
23 created databases will have the language installed automatically.
28 As of <productname>PostgreSQL</productname> 7.4, PL/Python is only
29 available as an <quote>untrusted</> language (meaning it does not
30 offer any way of restricting what users can do in it). It has
31 therefore been renamed to <literal>plpythonu</>. The trusted
32 variant <literal>plpython</> may become available again in future,
33 if a new secure execution mechanism is developed in Python.
38 Users of source packages must specially enable the build of
39 PL/Python during the installation process. (Refer to the
40 installation instructions for more information.) Users of binary
41 packages might find PL/Python in a separate subpackage.
45 <sect1 id="plpython-funcs">
46 <title>PL/Python Functions</title>
49 The Python code you write gets transformed into a Python function. E.g.,
51 CREATE FUNCTION myfunc(text) RETURNS text
59 def __plpython_procedure_myfunc_23456():
63 assuming that 23456 is the OID of the function.
67 If you do not provide a return value, Python returns the default
68 <symbol>None</symbol>. The language module translates Python's
69 <symbol>None</symbol> into the SQL null
70 value.<indexterm><primary>null value</><secondary
71 sortas="PL/Python">in PL/Python</></indexterm>
75 The <productname>PostgreSQL</> function parameters are available in
76 the global <varname>args</varname> list. In the
77 <function>myfunc</function> example, <varname>args[0]</> contains
78 whatever was passed in as the text argument. For
79 <literal>myfunc2(text, integer)</literal>, <varname>args[0]</>
80 would contain the <type>text</type> argument and
81 <varname>args[1]</varname> the <type>integer</type> argument.
85 The global dictionary <varname>SD</varname> is available to store
86 data between function calls. This variable is private static data.
87 The global dictionary <varname>GD</varname> is public data,
88 available to all Python functions within a session. Use with
89 care.<indexterm><primary>global data</><secondary>in
90 PL/Python</></indexterm>
94 Each function gets its own execution environment in the
95 Python interpreter, so that global data and function arguments from
96 <function>myfunc</function> are not available to
97 <function>myfunc2</function>. The exception is the data in the
98 <varname>GD</varname> dictionary, as mentioned above.
102 <sect1 id="plpython-trigger">
103 <title>Trigger Functions</title>
105 <indexterm zone="plpython-trigger">
106 <primary>trigger</primary>
107 <secondary>in PL/Python</secondary>
111 When a function is used in a trigger, the dictionary
112 <literal>TD</literal> contains trigger-related values. The trigger
113 rows are in <literal>TD["new"]</> and/or <literal>TD["old"]</>
114 depending on the trigger event. <literal>TD["event"]</> contains
115 the event as a string (<literal>INSERT</>, <literal>UPDATE</>,
116 <literal>DELETE</>, or <literal>UNKNOWN</>).
117 <literal>TD["when"]</> contains one of <literal>BEFORE</>,
118 <literal>AFTER</>, and <literal>UNKNOWN</>.
119 <literal>TD["level"]</> contains one of <literal>ROW</>,
120 <literal>STATEMENT</>, and <literal>UNKNOWN</>.
121 <literal>TD["name"]</> contains the trigger name, and
122 <literal>TD["relid"]</> contains the OID of the table on
123 which the trigger occurred. If the trigger was called with
124 arguments they are available in <literal>TD["args"][0]</> to
125 <literal>TD["args"][(n-1)]</>.
129 If <literal>TD["when"]</literal> is <literal>BEFORE</>, you may
130 return <literal>None</literal> or <literal>"OK"</literal> from the
131 Python function to indicate the row is unmodified,
132 <literal>"SKIP"</> to abort the event, or <literal>"MODIFY"</> to
133 indicate you've modified the row.
137 <sect1 id="plpython-database">
138 <title>Database Access</title>
141 The PL/Python language module automatically imports a Python module
142 called <literal>plpy</literal>. The functions and constants in
143 this module are available to you in the Python code as
144 <literal>plpy.<replaceable>foo</replaceable></literal>. At present
145 <literal>plpy</literal> implements the functions
146 <literal>plpy.debug("msg")</literal>,
147 <literal>plpy.log("msg")</literal>,
148 <literal>plpy.info("msg")</literal>,
149 <literal>plpy.notice("msg")</literal>,
150 <literal>plpy.warning("msg")</literal>,
151 <literal>plpy.error("msg")</literal>, and
152 <literal>plpy.fatal("msg")</literal>. They are mostly equivalent
153 to calling <literal>elog(<replaceable>LEVEL</>, "msg")</literal>
154 from C code.<indexterm><primary>elog</><secondary>in
155 PL/Python</></indexterm> <function>plpy.error</function> and
156 <function>plpy.fatal</function> actually raise a Python exception
157 which, if uncaught, causes the PL/Python module to call
158 <literal>elog(ERROR, msg)</literal> when the function handler
159 returns from the Python interpreter. Long-jumping out of the
160 Python interpreter is probably not good. <literal>raise
161 plpy.ERROR("msg")</literal> and <literal>raise
162 plpy.FATAL("msg")</literal> are equivalent to calling
163 <function>plpy.error</function> and
164 <function>plpy.fatal</function>, respectively.
168 Additionally, the <literal>plpy</literal> module provides two
169 functions called <function>execute</function> and
170 <function>prepare</function>. Calling
171 <function>plpy.execute</function> with a query string and an
172 optional limit argument causes that query to be run and the result
173 to be returned in a result object. The result object emulates a
174 list or dictionary object. The result object can be accessed by
175 row number and column name. It has these additional methods:
176 <function>nrows</function> which returns the number of rows
177 returned by the query, and <function>status</function> which is the
178 <function>SPI_exec()</function> return value. The result object
185 rv = plpy.execute("SELECT * FROM my_table", 5)
187 returns up to 5 rows from <literal>my_table</literal>. If
188 <literal>my_table</literal> has a column
189 <literal>my_column</literal>, it would be accessed as
191 foo = rv[i]["my_column"]
196 <indexterm><primary>preparing a query</><secondary>in PL/Python</></indexterm>
197 The second function, <function>plpy.prepare</function>, prepares
198 the execution plan for a query. It is called with a query string
199 and a list of parameter types, if you have parameter references in
200 the query. For example:
202 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])
204 <literal>text</literal> is the type of the variable you will be
205 passing for <literal>$1</literal>. After preparing a statement, you
206 use the function <function>plpy.execute</function> to run it:
208 rv = plpy.execute(plan, [ "name" ], 5)
210 The third argument is the limit and is optional.
214 In the current version, any database error encountered while
215 running a <application>PL/Python</application> function will result
216 in the immediate termination of that function by the server; it is
217 not possible to trap error conditions using Python <literal>try
218 ... catch</literal> constructs. For example, a syntax error in an
219 SQL statement passed to the <literal>plpy.execute</literal> call
220 will terminate the function. This behavior may be changed in a
225 When you prepare a plan using the PL/Python module it is
226 automatically saved. Read the SPI documentation (<xref
227 linkend="spi">) for a description of what this means.
228 In order to make effective use of this across function calls
229 one needs to use one of the persistent storage dictionaries
230 <literal>SD</literal> or <literal>GD</literal> (see
231 <xref linkend="plpython-funcs">). For example:
233 CREATE FUNCTION usesavedplan() RETURNS trigger AS '
234 if SD.has_key("plan"):
237 plan = plpy.prepare("SELECT 1")
240 ' LANGUAGE plpythonu;
246 <!-- NOT CURRENTLY SUPPORTED -->
248 <sect1 id="plpython-trusted">
249 <title>Restricted Environment</title>
252 The current version of <application>PL/Python</application>
253 functions as a trusted language only; access to the file system and
254 other local resources is disabled. Specifically,
255 <application>PL/Python</application> uses the Python restricted
256 execution environment, further restricts it to prevent the use of
257 the file <function>open</> call, and allows only modules from a
258 specific list to be imported. Presently, that list includes:
259 <literal>array</>, <literal>bisect</>, <literal>binascii</>,
260 <literal>calendar</>, <literal>cmath</>, <literal>codecs</>,
261 <literal>errno</>, <literal>marshal</>, <literal>math</>, <literal>md5</>,
262 <literal>mpz</>, <literal>operator</>, <literal>pcre</>,
263 <literal>pickle</>, <literal>random</>, <literal>re</>, <literal>regex</>,
264 <literal>sre</>, <literal>sha</>, <literal>string</>, <literal>StringIO</>,
265 <literal>struct</>, <literal>time</>, <literal>whrandom</>, and