1 <!-- $Header: /cvsroot/pgsql/doc/src/sgml/plpython.sgml,v 1.1 2001/05/12 17:49:32 petere Exp $ -->
3 <chapter id="plpython">
4 <title>PL/Python - Python Procedural Language</title>
8 This chapter is not fully developed yet.
12 <sect1 id="plpython-install">
13 <title>Installation</title>
16 ... needs to be worked out.
20 <sect1 id="plpython-using">
24 There are sample functions in
25 <filename>plpython_function.sql</filename>. The Python code you
26 write gets transformed into a function. E.g.,
28 CREATE FUNCTION myfunc(text) RETURNS text AS
36 def __plpython_procedure_myfunc_23456():
40 where 23456 is the Oid of the function.
44 If you do not provide a return value, Python returns the default
45 <symbol>None</symbol> which may or may not be what you want. The
46 language module translates Python's None into SQL NULL.
50 PostgreSQL function variables are available in the global
51 <varname>args</varname> list. In the <function>myfunc</function>
52 example, args[0] contains whatever was passed in as the text
53 argument. For <literal>myfunc2(text, int4)</literal>, args[0]
54 would contain the text variable and args[1] the int4 variable.
58 The global dictionary SD is available to store data between
59 function calls. This variable is private static data. The global
60 dictionary GD is public data, available to all python functions
61 within a backend. Use with care. When the function is used in a
62 trigger, the triggers tuples are in TD["new"] and/or TD["old"]
63 depending on the trigger event. Return 'None' or "OK" from the
64 python function to indicate the tuple is unmodified, "SKIP" to
65 abort the event, or "MODIFIED" to indicate you've modified the
66 tuple. If the trigger was called with arguments they are available
67 in TD["args"][0] to TD["args"][(n -1)]
71 Each function gets its own restricted execution object in the
72 Python interpreter, so that global data and function arguments from
73 <function>myfunc</function> are not available to
74 <function>myfunc2</function>. The exception is the data in the GD
75 dictionary, as mentioned above.
79 The PL/Python language module automatically imports a Python module
80 called <literal>plpy</literal>. The functions and constants in
81 this module are available to you in the Python code as
82 <literal>plpy.<replaceable>foo</replaceable></literal>. At present
83 <literal>plpy</literal> implements the functions
84 <literal>plpy.error("msg")</literal>,
85 <literal>plpy.fatal("msg")</literal>,
86 <literal>plpy.debug("msg")</literal>, and
87 <literal>plpy.notice("msg")</literal>. They are mostly equivalent
88 to calling <literal>elog(<replaceable>LEVEL</>, "msg")</literal>,
89 where <replaceable>LEVEL</> is DEBUG, ERROR, FATAL or NOTICE.
90 <function>plpy.error</function> and <function>plpy.fatal</function>
91 actually raise a Python exception which, if uncaught, causes the
92 PL/Python module to call <literal>elog(ERROR, msg)</literal> when
93 the function handler returns from the Python interpreter. Long
94 jumping out of the Python interpreter is probably not good.
95 <literal>raise plpy.ERROR("msg")</literal> and <literal>raise
96 plpy.FATAL("msg")</literal> are equivalent to calling
97 <function>plpy.error</function> or <function>plpy.fatal</function>.
101 Additionally, the plpy module provides two functions called
102 <function>execute</function> and <function>prepare</function>.
103 Calling <function>plpy.execute</function> with a query string, and
104 an optional limit argument, causes that query to be run, and the
105 result returned in a result object. The result object emulates a
106 list or dictionary object. The result object can be accessed by
107 row number, and field name. It has these additional methods:
108 <function>nrows()</function> which returns the number of rows
109 returned by the query, and <function>status</function> which is the
110 <function>SPI_exec</function> return variable. The result object
114 rv = plpy.execute("SELECT * FROM my_table", 5)
116 returns up to 5 rows from my_table. Ff my_table has a column
117 my_field it would be accessed as
119 foo = rv[i]["my_field"]
121 The second function <function>plpy.prepare</function> is called
122 with a query string, and a list of argument types if you have bind
123 variables in the query.
125 plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])
127 text is the type of the variable you will be passing as $1. After
128 preparing you use the function <function>plpy.execute</function> to
131 rv = plpy.execute(plan, [ "name" ], 5)
133 The limit argument is optional in the call to
134 <function>plpy.execute</function>.
138 When you prepare a plan using the PL/Python module it is
139 automatically saved. Read the SPI documentation (<xref
140 linkend="spi">) for a description of what this means. The take
141 home message is if you do
143 plan = plpy.prepare("SOME QUERY")
144 plan = plpy.prepare("SOME OTHER QUERY")
146 you are leaking memory, as I know of no way to free a saved plan.
147 The alternative of using unsaved plans it even more painful (for