1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/plpython.sgml,v 1.5 2001/11/12 19:19:39 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</></>
11 This chapter is not fully developed yet.
15 <sect1 id="plpython-install">
16 <title>Installation</title>
19 ... needs to be worked out.
23 <sect1 id="plpython-using">
27 There are sample functions in
28 <filename>plpython_function.sql</filename>. The Python code you
29 write gets transformed into a function. E.g.,
31 CREATE FUNCTION myfunc(text) RETURNS text AS
39 def __plpython_procedure_myfunc_23456():
43 where 23456 is the Oid of the function.
47 If you do not provide a return value, Python returns the default
48 <symbol>None</symbol> which may or may not be what you want. The
49 language module translates Python's None into SQL NULL.
53 PostgreSQL function variables are available in the global
54 <varname>args</varname> list. In the <function>myfunc</function>
55 example, <varname>args[0]</> contains whatever was passed in as the text
56 argument. For <literal>myfunc2(text, integer)</literal>, <varname>args[0]</>
57 would contain the <type>text</type> variable and <varname>args[1]</varname> the <type>integer</type> variable.
61 The global dictionary SD is available to store data between
62 function calls. This variable is private static data. The global
63 dictionary GD is public data, available to all python functions
64 within a backend. Use with care.
68 Each function gets its own restricted execution object in the
69 Python interpreter, so that global data and function arguments from
70 <function>myfunc</function> are not available to
71 <function>myfunc2</function>. The exception is the data in the GD
72 dictionary, as mentioned above.
76 When a function is used in a trigger, the dictionary TD contains
77 transaction related values. The trigger tuples are in <literal>TD["new"]</>
78 and/or <literal>TD["old"]</> depending on the trigger event. <literal>TD["event"]</>
79 contains the event as a string (<literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>, or
80 <literal>UNKNOWN</>). TD["when"] contains one of (<literal>BEFORE</>, <literal>AFTER</>, or
81 <literal>UNKNOWN</>). <literal>TD["level"]</> contains one of <literal>ROW</>, <literal>STATEMENT</>, or
82 <literal>UNKNOWN</>. <literal>TD["name"]</> contains the trigger name, and <literal>TD["relid"]</>
83 contains the relation id of the table on which the trigger occurred.
84 If the trigger was called with arguments they are available
85 in <literal>TD["args"][0]</> to <literal>TD["args"][(n -1)]</>.
89 If the trigger <quote>when</quote> is <literal>BEFORE</>, you may return <literal>None</literal> or <literal>"OK"</literal>
90 from the Python function to indicate the tuple is unmodified,
91 <literal>"SKIP"</> to abort the event, or <literal>"MODIFIED"</> to indicate you've
96 The PL/Python language module automatically imports a Python module
97 called <literal>plpy</literal>. The functions and constants in
98 this module are available to you in the Python code as
99 <literal>plpy.<replaceable>foo</replaceable></literal>. At present
100 <literal>plpy</literal> implements the functions
101 <literal>plpy.error("msg")</literal>,
102 <literal>plpy.fatal("msg")</literal>,
103 <literal>plpy.debug("msg")</literal>, and
104 <literal>plpy.notice("msg")</literal>. They are mostly equivalent
105 to calling <literal>elog(<replaceable>LEVEL</>, "msg")</literal>,
106 where <replaceable>LEVEL</> is DEBUG, ERROR, FATAL or NOTICE.
107 <function>plpy.error</function> and <function>plpy.fatal</function>
108 actually raise a Python exception which, if uncaught, causes the
109 PL/Python module to call <literal>elog(ERROR, msg)</literal> when
110 the function handler returns from the Python interpreter. Long
111 jumping out of the Python interpreter is probably not good.
112 <literal>raise plpy.ERROR("msg")</literal> and <literal>raise
113 plpy.FATAL("msg")</literal> are equivalent to calling
114 <function>plpy.error</function> or <function>plpy.fatal</function>.
118 Additionally, the <literal>plpy</literal> module provides two functions called
119 <function>execute</function> and <function>prepare</function>.
120 Calling <function>plpy.execute</function> with a query string, and
121 an optional limit argument, causes that query to be run, and the
122 result returned in a result object. The result object emulates a
123 list or dictionary object. The result object can be accessed by
124 row number, and field name. It has these additional methods:
125 <function>nrows()</function> which returns the number of rows
126 returned by the query, and <function>status</function> which is the
127 <function>SPI_exec</function> return variable. The result object
131 rv = plpy.execute("SELECT * FROM my_table", 5)
133 returns up to 5 rows from my_table. Ff my_table has a column
134 my_field it would be accessed as
136 foo = rv[i]["my_field"]
138 The second function <function>plpy.prepare</function> is called
139 with a query string, and a list of argument types if you have bind
140 variables in the query.
142 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])
144 text is the type of the variable you will be passing as $1. After
145 preparing you use the function <function>plpy.execute</function> to
148 rv = plpy.execute(plan, [ "name" ], 5)
150 The limit argument is optional in the call to
151 <function>plpy.execute</function>.
155 When you prepare a plan using the PL/Python module it is
156 automatically saved. Read the SPI documentation (<xref
157 linkend="spi">) for a description of what this means. The take
158 home message is if you do
160 plan = plpy.prepare("SOME QUERY")
161 plan = plpy.prepare("SOME OTHER QUERY")
163 you are leaking memory, as I know of no way to free a saved plan.
164 The alternative of using unsaved plans it even more painful (for