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. Functions in PL/Python are declared via the usual syntax. 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 valuein 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 datain 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 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. 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).elogin 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 queryin 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; 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. ]]>