41.3. Database Access

The PL/Python language module automatically imports a Python module calledplpy. The functions and constants in this module are available to you in the Python code asplpy.foo. At presentplpy implements the functionsplpy.debug(msg),plpy.log(msg),plpy.info(msg),plpy.notice(msg),plpy.warning(msg),plpy.error(msg), andplpy.fatal(msg).plpy.error andplpy.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) andraise plpy.FATAL(msg) are equivalent to callingplpy.error andplpy.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 thelog_min_messages andclient_min_messages configuration variables. SeeChapter 18 for more information.

Additionally, theplpy module provides two functions calledexecute andprepare. Callingplpy.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, andstatus which is theSPI_execute() return value. The result object can be modified.

For example:

rv = plpy.execute("SELECT * FROM my_table", 5)

returns up to 5 rows frommy_table. Ifmy_table has a columnmy_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 functionplpy.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 42) 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 dictionariesSD orGD (seeSection 41.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;