1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>39.3.Database Access</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="plpython.html" title="Chapter39.PL/Python - Python Procedural Language">
<link rel="prev" href="plpython-trigger.html" title="39.2.Trigger Functions">
<link rel="next" href="spi.html" title="Chapter40.Server Programming Interface">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="plpython-database"></a>39.3.Database Access</h2></div></div></div>
<p> The PL/Python language module automatically imports a Python module
called <code class="literal">plpy</code>. The functions and constants in
this module are available to you in the Python code as
<code class="literal">plpy.<em class="replaceable"><code>foo</code></em></code>. At present
<code class="literal">plpy</code> implements the functions
<code class="literal">plpy.debug(<em class="replaceable"><code>msg</code></em>)</code>,
<code class="literal">plpy.log(<em class="replaceable"><code>msg</code></em>)</code>,
<code class="literal">plpy.info(<em class="replaceable"><code>msg</code></em>)</code>,
<code class="literal">plpy.notice(<em class="replaceable"><code>msg</code></em>)</code>,
<code class="literal">plpy.warning(<em class="replaceable"><code>msg</code></em>)</code>,
<code class="literal">plpy.error(<em class="replaceable"><code>msg</code></em>)</code>, and
<code class="literal">plpy.fatal(<em class="replaceable"><code>msg</code></em>)</code>.<a name="id733554"></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.
<code class="literal">raise plpy.ERROR(<em class="replaceable"><code>msg</code></em>)</code> and
<code class="literal">raise plpy.FATAL(<em class="replaceable"><code>msg</code></em>)</code> 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-logging.html#guc-log-min-messages">log_min_messages</a> and
<a href="runtime-config-logging.html#guc-client-min-messages">client_min_messages</a> configuration
variables. See <a href="runtime-config.html" title="Chapter17.Server Configuration">Chapter17, <i>Server Configuration</i></a> for more information.
</p>
<p> Additionally, the <code class="literal">plpy</code> 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 <code class="literal">my_table</code>. If
<code class="literal">my_table</code> has a column
<code class="literal">my_column</code>, it would be accessed as
</p>
<pre class="programlisting">foo = rv[i]["my_column"]</pre>
<p>
</p>
<p> <a name="id733738"></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>
<code class="literal">text</code> is the type of the variable you will be
passing for <code class="literal">$1</code>. 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" title="Chapter40.Server Programming Interface">Chapter40, <i>Server Programming Interface</i></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
<code class="literal">SD</code> or <code class="literal">GD</code> (see
<a href="plpython.html#plpython-funcs" title="39.1.PL/Python Functions">Section39.1, “PL/Python Functions”</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></body>
</html>
|