<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML
><HEAD
><TITLE
>Database Access</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 7.4.1 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
NAME="creation"
CONTENT="2003-12-22T03:48:47"></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 7.4.1 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 40. 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"
>40.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.<VAR
CLASS="REPLACEABLE"
>foo</VAR
></TT
>. At present
<TT
CLASS="LITERAL"
>plpy</TT
> implements the functions
<TT
CLASS="LITERAL"
>plpy.debug("msg")</TT
>,
<TT
CLASS="LITERAL"
>plpy.log("msg")</TT
>,
<TT
CLASS="LITERAL"
>plpy.info("msg")</TT
>,
<TT
CLASS="LITERAL"
>plpy.notice("msg")</TT
>,
<TT
CLASS="LITERAL"
>plpy.warning("msg")</TT
>,
<TT
CLASS="LITERAL"
>plpy.error("msg")</TT
>, and
<TT
CLASS="LITERAL"
>plpy.fatal("msg")</TT
>. They are mostly equivalent
to calling <TT
CLASS="LITERAL"
>elog(<VAR
CLASS="REPLACEABLE"
>LEVEL</VAR
>, "msg")</TT
>
from C code.<A
NAME="AEN33108"
></A
> <CODE
CLASS="FUNCTION"
>plpy.error</CODE
> and
<CODE
CLASS="FUNCTION"
>plpy.fatal</CODE
> actually raise a Python exception
which, if uncaught, causes the PL/Python module to call
<TT
CLASS="LITERAL"
>elog(ERROR, msg)</TT
> when the function handler
returns from the Python interpreter. Long-jumping out of the
Python interpreter is probably not good. <TT
CLASS="LITERAL"
>raise
plpy.ERROR("msg")</TT
> and <TT
CLASS="LITERAL"
>raise
plpy.FATAL("msg")</TT
> are equivalent to calling
<CODE
CLASS="FUNCTION"
>plpy.error</CODE
> and
<CODE
CLASS="FUNCTION"
>plpy.fatal</CODE
>, respectively.
</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_exec()</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="AEN33133"
></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
> In the current version, any database error encountered while
running a <SPAN
CLASS="APPLICATION"
>PL/Python</SPAN
> function will result
in the immediate termination of that function by the server; it is
not possible to trap error conditions using Python <TT
CLASS="LITERAL"
>try
... catch</TT
> constructs. For example, a syntax error in an
SQL statement passed to the <TT
CLASS="LITERAL"
>plpy.execute</TT
> call
will terminate the function. This behavior may be changed in a
future release.
</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 41</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 40.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
>
