PL/Python - Python Procedural Language
PL/Python>>
Python>>
The PL/Python procedural language allows
PostgreSQL functions to be written in the
Python language,.
To install PL/Python in a particular database, use
createlang plpythonu dbname>.
If a language is installed into template1>, all subsequently
created databases will have the language installed automatically.
As of PostgreSQL 7.4, PL/Python is only
available as an untrusted> language (meaning it does not
offer any way of restricting what users can do in it). It has
therefore been renamed to plpythonu>. The trusted
variant plpython> may become available again in future,
if a new secure execution mechanism is developed in Python.
Users of source packages must specially enable the build of
PL/Python during the installation process. (Refer to the
installation instructions for more information.) Users of binary
packages might find PL/Python in a separate subpackage.
PL/Python Functions
Functions in PL/Python are declared in the usual way, for example
CREATE FUNCTION myfunc(text) RETURNS text
AS 'return args[0]'
LANGUAGE plpythonu;
The Python code that is given as the body of the function definition
gets transformed into a Python function.
For example, the above results in
def __plpython_procedure_myfunc_23456():
return args[0]
assuming that 23456 is the OID assigned to the function by
PostgreSQL.
If you do not provide a return value, Python returns the default
None. PL/Python translates
Python's None into the SQL null
value.null value>in PL/Python>
The PostgreSQL> function parameters are available in
the global args list. In the
myfunc example, args[0]> contains
whatever was passed in as the text argument. For
myfunc2(text, integer), args[0]>
would contain the text argument and
args[1] the integer argument.
The global dictionary SD is available to store
data between function calls. This variable is private static data.
The global dictionary GD is public data,
available to all Python functions within a session. Use with
care.global data>in
PL/Python>
Each function gets its own execution environment in the
Python interpreter, so that global data and function arguments from
myfunc are not available to
myfunc2. The exception is the data in the
GD dictionary, as mentioned above.
Trigger Functions
trigger
in PL/Python
When a function is used as a trigger, the dictionary
TD contains trigger-related values. The trigger
rows are in TD["new"]> and/or TD["old"]>
depending on the trigger event. TD["event"]> contains
the event as a string (INSERT>, UPDATE>,
DELETE>, or UNKNOWN>).
TD["when"]> contains one of BEFORE>,
AFTER>, and UNKNOWN>.
TD["level"]> contains one of ROW>,
STATEMENT>, and UNKNOWN>.
TD["name"]> contains the trigger name, and
TD["relid"]> contains the OID of the table on
which the trigger occurred. If the CREATE TRIGGER> command
included arguments, they are available in TD["args"][0]> to
TD["args"][(n>-1)]>.
If TD["when"] is BEFORE>, you may
return None or "OK" from the
Python function to indicate the row is unmodified,
"SKIP"> to abort the event, or "MODIFY"> to
indicate you've modified the row.
Database Access
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>).elog>in PL/Python>
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
and
configuration
variables. See 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"]
preparing a query>in PL/Python>
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 () 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
). 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;
Restricted Environment
The current version of PL/Python
functions as a trusted language only; access to the file system and
other local resources is disabled. Specifically,
PL/Python uses the Python restricted
execution environment, further restricts it to prevent the use of
the file open> call, and allows only modules from a
specific list to be imported. Presently, that list includes:
array>, bisect>, binascii>,
calendar>, cmath>, codecs>,
errno>, marshal>, math>, md5>,
mpz>, operator>, pcre>,
pickle>, random>, re>, regex>,
sre>, sha>, string>, StringIO>,
struct>, time>, whrandom>, and
zlib>.
]]>