The PL/Python language module automatically imports a Python module
called plpy. The functions and constants in
this module are available to you in the Python code as
plpy.foo. At present
plpy implements the functions
plpy.debug("msg"),
plpy.log("msg"),
plpy.info("msg"),
plpy.notice("msg"),
plpy.warning("msg"),
plpy.error("msg"), and
plpy.fatal("msg"). They are mostly equivalent
to calling elog(LEVEL, "msg")
from C code. plpy.error and
plpy.fatal actually raise a Python exception
which, if uncaught, causes the PL/Python module to call
elog(ERROR, msg) when the function handler
returns from the Python interpreter. Long-jumping out of the
Python interpreter is probably not good. raise
plpy.ERROR("msg") and raise
plpy.FATAL("msg") are equivalent to calling
plpy.error and
plpy.fatal, respectively.
Additionally, the plpy module provides two
functions called execute and
prepare. Calling
plpy.execute with a query string and an
optional limit argument causes that query to be run and the result
to be returned in a result object. The result object emulates a
list or dictionary object. The result object can be accessed by
row number and field name. It has these additional methods:
nrows() which returns the number of rows
returned by the query, and status which is the
SPI_exec return variable. The result object
can be modified.
For example,
rv = plpy.execute("SELECT * FROM my_table", 5)returns up to 5 rows from my_table. If my_table has a column my_field, it would be accessed as
foo = rv[i]["my_field"]
The second function plpy.prepare is called
with a query string and a list of argument types if you have bind
variables in the query. For example:
plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])
text is the type of the variable you will be
passing as $1. After preparing a statement, you
use the function plpy.execute to run it:
rv = plpy.execute(plan, [ "name" ], 5)
The limit argument is optional in the call to
plpy.execute.
In the current version, any database error encountered while running a PL/Python function will result in the immediate termination of that function by the server; it is not possible to trap error conditions using Python try ... catch constructs. For example, a syntax error in an SQL statement passed to the plpy.execute() call will terminate the function. This behavior may be changed in a future release.
When you prepare a plan using the PL/Python module it is automatically saved. Read the SPI documentation (Chapter 17) for a description of what this means.
In order to make effective use of this across function calls one needs to use one of the persistent storage dictionaries SD or GD, see Section 22.1. For example:
CREATE FUNCTION usesavedplan ( ) RETURNS TRIGGER AS '
if SD.has_key("plan"):
plan = SD["plan"]
else:
plan = plpy.prepare("SELECT 1")
SD["plan"] = plan
# rest of function
' LANGUAGE 'plpython';