<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Database Access</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="PL/Python - Python Procedural Language" HREF="plpython.html"><LINK REL="PREVIOUS" TITLE="Trigger Functions" HREF="plpython-trigger.html"><LINK REL="NEXT" TITLE="Server Programming Interface" HREF="spi.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="plpython-trigger.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="plpython.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 38. PL/Python - Python Procedural Language</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="plpython.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="spi.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="PLPYTHON-DATABASE" >38.3. Database Access</A ></H1 ><P > The PL/Python language module automatically imports a Python module called <TT CLASS="LITERAL" >plpy</TT >. The functions and constants in this module are available to you in the Python code as <TT CLASS="LITERAL" >plpy.<TT CLASS="REPLACEABLE" ><I >foo</I ></TT ></TT >. At present <TT CLASS="LITERAL" >plpy</TT > implements the functions <TT CLASS="LITERAL" >plpy.debug(<TT CLASS="REPLACEABLE" ><I >msg</I ></TT >)</TT >, <TT CLASS="LITERAL" >plpy.log(<TT CLASS="REPLACEABLE" ><I >msg</I ></TT >)</TT >, <TT CLASS="LITERAL" >plpy.info(<TT CLASS="REPLACEABLE" ><I >msg</I ></TT >)</TT >, <TT CLASS="LITERAL" >plpy.notice(<TT CLASS="REPLACEABLE" ><I >msg</I ></TT >)</TT >, <TT CLASS="LITERAL" >plpy.warning(<TT CLASS="REPLACEABLE" ><I >msg</I ></TT >)</TT >, <TT CLASS="LITERAL" >plpy.error(<TT CLASS="REPLACEABLE" ><I >msg</I ></TT >)</TT >, and <TT CLASS="LITERAL" >plpy.fatal(<TT CLASS="REPLACEABLE" ><I >msg</I ></TT >)</TT >.<A NAME="AEN34505" ></A > <CODE CLASS="FUNCTION" >plpy.error</CODE > and <CODE CLASS="FUNCTION" >plpy.fatal</CODE > actually raise a Python exception which, if uncaught, propagates out to the calling query, causing the current transaction or subtransaction to be aborted. <TT CLASS="LITERAL" >raise plpy.ERROR(<TT CLASS="REPLACEABLE" ><I >msg</I ></TT >)</TT > and <TT CLASS="LITERAL" >raise plpy.FATAL(<TT CLASS="REPLACEABLE" ><I >msg</I ></TT >)</TT > are equivalent to calling <CODE CLASS="FUNCTION" >plpy.error</CODE > and <CODE CLASS="FUNCTION" >plpy.fatal</CODE >, 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 <A HREF="runtime-config.html#GUC-LOG-MIN-MESSAGES" >log_min_messages</A > and <A HREF="runtime-config.html#GUC-CLIENT-MIN-MESSAGES" >client_min_messages</A > configuration variables. See <A HREF="runtime-config.html" >Section 16.4</A > for more information. </P ><P > Additionally, the <TT CLASS="LITERAL" >plpy</TT > module provides two functions called <CODE CLASS="FUNCTION" >execute</CODE > and <CODE CLASS="FUNCTION" >prepare</CODE >. Calling <CODE CLASS="FUNCTION" >plpy.execute</CODE > 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: <CODE CLASS="FUNCTION" >nrows</CODE > which returns the number of rows returned by the query, and <CODE CLASS="FUNCTION" >status</CODE > which is the <CODE CLASS="FUNCTION" >SPI_execute()</CODE > return value. The result object can be modified. </P ><P > For example, </P><PRE CLASS="PROGRAMLISTING" >rv = plpy.execute("SELECT * FROM my_table", 5)</PRE ><P> returns up to 5 rows from <TT CLASS="LITERAL" >my_table</TT >. If <TT CLASS="LITERAL" >my_table</TT > has a column <TT CLASS="LITERAL" >my_column</TT >, it would be accessed as </P><PRE CLASS="PROGRAMLISTING" >foo = rv[i]["my_column"]</PRE ><P> </P ><P > <A NAME="AEN34534" ></A > The second function, <CODE CLASS="FUNCTION" >plpy.prepare</CODE >, 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: </P><PRE CLASS="PROGRAMLISTING" >plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ])</PRE ><P> <TT CLASS="LITERAL" >text</TT > is the type of the variable you will be passing for <TT CLASS="LITERAL" >$1</TT >. After preparing a statement, you use the function <CODE CLASS="FUNCTION" >plpy.execute</CODE > to run it: </P><PRE CLASS="PROGRAMLISTING" >rv = plpy.execute(plan, [ "name" ], 5)</PRE ><P> The third argument is the limit and is optional. </P ><P > When you prepare a plan using the PL/Python module it is automatically saved. Read the SPI documentation (<A HREF="spi.html" >Chapter 39</A >) 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 <TT CLASS="LITERAL" >SD</TT > or <TT CLASS="LITERAL" >GD</TT > (see <A HREF="plpython.html#PLPYTHON-FUNCS" >Section 38.1</A >). For example: </P><PRE CLASS="PROGRAMLISTING" >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;</PRE ><P> </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="plpython-trigger.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="spi.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Trigger Functions</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="plpython.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Server Programming Interface</TD ></TR ></TABLE ></DIV ></BODY ></HTML >