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 plpython dbname. If a language is installed into template1, all subsequently created databases will have the language installed automatically. 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. The Python code you write gets transformed into a Python function. E.g., CREATE FUNCTION myfunc(text) RETURNS text AS 'return args[0]' LANGUAGE plpython; gets transformed into def __plpython_procedure_myfunc_23456(): return args[0] where 23456 is the OID of the function. If you do not provide a return value, Python returns the default None. The language module translates Python's None into the SQL null value. 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. Each function gets its own restricted execution object 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. When a function is used in 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 trigger was called with 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. 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 column 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 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. 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 () 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 plpython; 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.