|
|
PostgreSQL 8.2.6 Documentation | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 40. PL/Python - Python Procedural Language | Fast Forward | Next |
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).
plpy.error
and
plpy.fatal
actually raise a Python exception
which, if uncaught, propagates out to the calling query, causing
the current transaction or subtransaction to be aborted.
raise plpy.ERROR(msg) and
raise plpy.FATAL(msg) are
equivalent to calling
plpy.error
and
plpy.fatal
, respectively.
The other functions only generate messages of different
priority levels.
Whether messages of a particular priority are reported to the client,
written to the server log, or both is controlled by the
log_min_messages and
client_min_messages configuration
variables. See Chapter 17 for more information.
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 column name. It has these additional methods:
nrows
which returns the number of rows
returned by the query, and status
which is the
SPI_execute()
return value. 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_column, it would be accessed as
foo = rv[i]["my_column"]
The second function, plpy.prepare
, prepares
the execution plan for a query. It is called with a query string
and a list of parameter types, if you have parameter references 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 for $1. After preparing a statement, you
use the function plpy.execute
to run it:
rv = plpy.execute(plan, [ "name" ], 5)
The third argument is the limit and is optional.
When you prepare a plan using the PL/Python module it is automatically saved. Read the SPI documentation (Chapter 41) 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 40.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 plpythonu;