be/src/documentation/html/programming.html at v1.0 · python-postgres/be

940 lines (870 loc) · 56.4 KB
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Programming &mdash; pg-python v1.0.1 documentation</title>
    <link rel="stylesheet" href="_static/default.css" type="text/css" />
    <link rel="stylesheet" href="_static/pygments.css" type="text/css" />
    <script type="text/javascript">
      var DOCUMENTATION_OPTIONS = {
        URL_ROOT:    '',
        VERSION:     '1.0.1',
        COLLAPSE_INDEX: false,
        FILE_SUFFIX: '.html',
        HAS_SOURCE:  true
    </script>
    <script type="text/javascript" src="_static/jquery.js"></script>
    <script type="text/javascript" src="_static/underscore.js"></script>
    <script type="text/javascript" src="_static/doctools.js"></script>
    <link rel="top" title="pg-python v1.0.1 documentation" href="index.html" />
    <link rel="next" title="Gotchas" href="gotchas.html" />
    <link rel="prev" title="Requirements" href="admin.html" /> 
    <div class="related">
      <h3>Navigation</h3>
        <li class="right" style="margin-right: 10px">
          <a href="genindex.html" title="General Index"
             accesskey="I">index</a></li>
        <li class="right" >
          <a href="gotchas.html" title="Gotchas"
             accesskey="N">next</a> |</li>
        <li class="right" >
          <a href="admin.html" title="Requirements"
             accesskey="P">previous</a> |</li>
        <li><a href="index.html">pg-python v1.0.1 documentation</a> &raquo;</li> 
      </ul>
    </div>  
    <div class="document">
      <div class="documentwrapper">
        <div class="bodywrapper">
          <div class="body">
  <div class="section" id="programming">
<h1>Programming<a class="headerlink" href="#programming" title="Permalink to this headline">¶</a></h1>
<p>Python functions are Python modules that can be created using the
standard CREATE FUNCTION syntax:</p>
<div class="highlight-python"><pre>CREATE FUNCTION funcname(args)
RETURNS return_type LANGUAGE python AS
import Postgres
def main(*args):
       return value
$python$;</pre>
<p>When a Python function is called using SQL, the function&#8217;s module is imported,
and the entry point is called&#8211;normally <tt class="docutils literal"><span class="pre">main</span></tt>.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The term &#8220;Python function&#8221; is often used in reference to an SQL function
object whose language is <tt class="docutils literal"><span class="pre">python</span></tt>.</p>
<div class="section" id="builtins">
<span id="programming-builtins"></span><h2>Builtins<a class="headerlink" href="#builtins" title="Permalink to this headline">¶</a></h2>
<p>The interface module, <cite>Postgres</cite>, provides all of the
Python types and interface functions created by pg-python. In order to
provide convenient access to often used objects, some
additional built-ins are provided by pg-python.</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">&#64;pytypes</span></tt></dt>
<dd>Alias to <a class="reference internal" href="postgres.html#pg-pytypes"><em>Postgres.pytypes</em></a>.</dd>
<dt><tt class="docutils literal"><span class="pre">prepare(sql_statement)</span></tt></dt>
<dd>Alias to <a class="reference internal" href="postgres.html#pg-statement"><em>Postgres.Statement</em></a></dd>
<dt><tt class="docutils literal"><span class="pre">proc(procedure_id)</span></tt></dt>
<dd>Function that takes <tt class="docutils literal"><span class="pre">pg_catalog.regprocedure</span></tt> text or a
function <tt class="docutils literal"><span class="pre">Oid</span></tt> and returns the corresponding <a class="reference internal" href="postgres.html#pg-function"><em>Postgres.Function</em></a>.</dd>
<dt><tt class="docutils literal"><span class="pre">sqleval(sql_expr,</span> <span class="pre">*args)</span></tt></dt>
<dd>Alias to <a class="reference internal" href="postgres.html#pg-eval"><em>Postgres.eval</em></a>.</dd>
<dt><tt class="docutils literal"><span class="pre">sqlexec(str_of_sql_statements)</span></tt></dt>
<dd>Execute multiple SQL statements. Alias to <a class="reference internal" href="postgres.html#pg-execute"><em>Postgres.execute</em></a>.</dd>
<dt><tt class="docutils literal"><span class="pre">xact()</span></tt></dt>
<dd>Context manager that starts and ends an internal subtransaction.
Alias to <a class="reference internal" href="postgres.html#pg-transaction"><em>Postgres.Transaction</em></a>.</dd>
</div></blockquote>
<div class="section" id="normal-functions">
<h2>Normal Functions<a class="headerlink" href="#normal-functions" title="Permalink to this headline">¶</a></h2>
<p>Normal functions are executed by invoking the <tt class="docutils literal"><span class="pre">main</span></tt>
entry point defined by the function module. If it does not exist, a Python
<tt class="docutils literal"><span class="pre">AttributeError</span></tt> will be raised as a Postgres error.</p>
<p>The <tt class="docutils literal"><span class="pre">main</span></tt> entry point must be able to
take the same number of parameters as declared by the <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">FUNCTION</span></tt>
statement that created the Postgres function. All arguments are given as
positional arguments to the <tt class="docutils literal"><span class="pre">main</span></tt> object.</p>
<div class="admonition tip">
<p class="first admonition-title">Tip</p>
<p class="last"><tt class="docutils literal"><span class="pre">main</span></tt> does not have to be a regular function object.
Any callable object will suffice.</p>
<p>The following example shows the creation of a simple addition function:</p>
<div class="highlight-python"><pre>CREATE FUNCTION add(n1 int, n2 int)
RETURNS int LANGUAGE python AS
Return the sum of the two parameters.
def main(n1, n2):
    return n1 + n2
SELECT add(1,2);
--(1 row)</pre>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The above example makes use of the <tt class="docutils literal"><span class="pre">pytypes</span></tt> decorator.
This wrapper converts a few of the common data objects into standard
Python objects. See <cite>Postgres.pytypes</cite> for more details.</p>
<div class="section" id="set-returning-functions">
<h2>Set Returning Functions<a class="headerlink" href="#set-returning-functions" title="Permalink to this headline">¶</a></h2>
<p>Set returning functions are very much like normal functions. The difference
is that the result is expected to be an iterable of objects that can be
coerced into the result type.</p>
<p>The exhaustion of the iterable will signal the termination of the set
produced by the function:</p>
<div class="highlight-python"><pre>CREATE FUNCTION simple_strings()
RETURNS SETOF text LANGUAGE python AS
Return an iterable of strings.
def main():
    return ("Hello", "World", "!")
SELECT s FROM simple_strings() AS t(s);
--(3 rows)</pre>
<p>Any iterable can be returned. Notably, main can be a generator yielding the
objects to be coerced.</p>
<div class="section" id="trigger-returning-functions">
<span id="programming-trf"></span><h2>Trigger Returning Functions<a class="headerlink" href="#trigger-returning-functions" title="Permalink to this headline">¶</a></h2>
<p>Trigger functions, like normal functions, are Python modules. However,
unlike normal functions, trigger functions don&#8217;t use the
<tt class="docutils literal"><span class="pre">main</span></tt> entry point. Rather, trigger functions have
entry points for each possible event type&#8211;with respect to orientation,
timing, and manipulation. Multiple entry points can be defined in order to
allow the trigger function to handle multiple event types.</p>
<p>Trigger returning functions are used by <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TRIGGER</span></tt> statements to
designate the procedure that should be executed when the described event occurs.</p>
<p>Trigger functions are declared in the same fashion as normal functions,
using <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">FUNCTION</span></tt>:</p>
<div class="highlight-python"><pre>CREATE FUNCTION funcname()
RETURNS TRIGGER
LANGUAGE python AS
A trigger function that does nothing.
def before_insert(td, new):
$python$;</pre>
<p>When a trigger function&#8217;s entry point is invoked, it is given
<em>at least</em> one argument, the <a class="reference internal" href="postgres.html#pg-triggerdata"><em>Postgres.TriggerData</em></a> describing the event.
Additional arguments given to the entry point, if any, depend on the entry
point&#8211;each entry point takes a certain number of arguments that suites the
<div class="section" id="row-triggers">
<h3>Row Triggers<a class="headerlink" href="#row-triggers" title="Permalink to this headline">¶</a></h3>
<p>Row triggers can be fired for each row that is inserted, updated, or
deleted. The additional arguments given to a row trigger entry points
are records of the target table&#8217;s type&#8211;a subclass of
<a class="reference internal" href="postgres_types.html#pg-types-record"><em>Postgres.types.record</em></a>.
These arguments provide access to the row being inserted, updated, or deleted.</p>
<p>Row Trigger Entry Points:</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">before_insert(td,</span> <span class="pre">new):</span></tt></dt>
<dd><p class="first">The <tt class="docutils literal"><span class="pre">new</span></tt> argument is the row that will be inserted.</p>
<p class="last">Executed for each row, before the insertion is actually made. This entry
point can provide a substitute row or suppress the manipulation
entirely.</p>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">before_update(td,</span> <span class="pre">old,</span> <span class="pre">new):</span></tt></dt>
<dd><p class="first">The <tt class="docutils literal"><span class="pre">old</span></tt> argument is the row that is being updated
into the <tt class="docutils literal"><span class="pre">new</span></tt> argument.</p>
<p class="last">Executed for each row, before the update is actually made. This entry
point can provide a substitute row or suppress the manipulation
entirely.</p>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">before_delete(td,</span> <span class="pre">old):</span></tt></dt>
<dd><p class="first">The <tt class="docutils literal"><span class="pre">old</span></tt> argument is the row being removed.</p>
<p class="last">Executed for each row, before the row is actually removed. This entry
point can only suppress the manipulation.</p>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">after_insert(td,</span> <span class="pre">new):</span></tt></dt>
<dd>Executed for each row, after the insert occurs. The
<tt class="docutils literal"><span class="pre">new</span></tt> argument is the row that was inserted.
<tt class="xref docutils literal"><span class="pre">None</span></tt> <em>must</em> be returned.</dd>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">after_update(td,</span> <span class="pre">old,</span> <span class="pre">new):</span></tt></dt>
<dd>Executed for each row, after the update occurs. The
<tt class="docutils literal"><span class="pre">old</span></tt> argument is the row that was altered into the
<tt class="docutils literal"><span class="pre">new</span></tt> argument. <tt class="xref docutils literal"><span class="pre">None</span></tt> <em>must</em> must be returned.</dd>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">after_delete(td,</span> <span class="pre">old):</span></tt></dt>
<dd>Executed for each row, after the delete occurs. The
<tt class="docutils literal"><span class="pre">old</span></tt> argument is the row that was removed from the
table. <tt class="xref docutils literal"><span class="pre">None</span></tt> <em>must</em> must be returned.</dd>
</div></blockquote>
<p>The AFTER entry points require that the <tt class="xref docutils literal"><span class="pre">None</span></tt> object is
returned. If <tt class="xref docutils literal"><span class="pre">None</span></tt> is not returned, the procedural
language will throw an error.</p>
<div class="section" id="suppressing-manipulations">
<h4>Suppressing Manipulations<a class="headerlink" href="#suppressing-manipulations" title="Permalink to this headline">¶</a></h4>
<p>When a manipulation should not proceed, it can be suppressed by a
trigger. Suppression can only be enacted by triggers that are timed
before the manipulation occurs. Specifically,
<tt class="docutils literal"><span class="pre">before_insert</span></tt>, <tt class="docutils literal"><span class="pre">before_update</span></tt>, and
<tt class="docutils literal"><span class="pre">before_delete</span></tt> are the only entry points that can cause
suppression.</p>
<p>In order to signal that suppression should occur, the called entry point
will need to raise the <a class="reference internal" href="postgres.html#pg-stopevent"><em>Postgres.StopEvent</em></a> exception:</p>
<div class="highlight-python"><pre>CREATE TABLE single_column(t text);
INSERT INTO single_column VALUES ('removed'), ('kept');
CREATE FUNCTION suppress_removal()
RETURNS TRIGGER LANGUAGE python AS
from Postgres import StopEvent
def before_delete(td, old):
       if old['t'] == 'kept':
               # This does the trick.
               raise StopEvent
CREATE TRIGGER suppres_it BEFORE DELETE ON single_column
FOR EACH ROW EXECUTE PROCEDURE suppress_removal();
DELETE FROM single_column;
SELECT t FROM single_column;
--(1 row)</pre>
<div class="section" id="substituting-rows">
<h4>Substituting Rows<a class="headerlink" href="#substituting-rows" title="Permalink to this headline">¶</a></h4>
<p>When a different row should take the place of the one being inserted or
updated, substitution can be performed by
<em>returning the replacement row</em>. Substitution can only be used with row
triggers that are timed before the manipulation. Specifically, only
<tt class="docutils literal"><span class="pre">before_insert</span></tt> and <tt class="docutils literal"><span class="pre">before_update</span></tt>
can perform row substitution. If <tt class="xref docutils literal"><span class="pre">None</span></tt> is returned,
<em>the manipulation will continue unaltered</em>:</p>
<div class="highlight-python"><pre>CREATE TABLE two_columns(i int, t text);
CREATE FUNCTION substitute_row()
RETURNS TRIGGER LANGUAGE python AS
def before_insert(td, new):
       if new['i'] == 0:
               # .replace() creates a *new* record with `t` set to 'newval'
               return new.replace(t = 'newval')
       # Otherwise, None is returned by default, which causes
       # the manipulation to continue.
CREATE TRIGGER execsub BEFORE INSERT ON two_columns
FOR EACH ROW EXECUTE PROCEDURE substitute_row();
INSERT INTO two_columns VALUES (0, 'text');
SELECT i, t FROM two_columns;
-----+--------
-- 0 | newval
--(1 row)</pre>
<p>The object that is returned by the entry point will be coerced into a
record of the composite type of the trigger&#8217;s table. This means that a
simple Python tuple or dictionary could be returned in order to supply the
replacement. See <a class="reference internal" href="#composite-types">Composite Types</a> for more information.</p>
<div class="admonition tip">
<p class="first admonition-title">Tip</p>
<p class="last">The <tt class="docutils literal"><span class="pre">replace</span></tt> and <tt class="docutils literal"><span class="pre">transform</span></tt> methods
on records are often the most convenient mechanisms to create the
appropriate substitute. See the <a class="reference internal" href="postgres_types.html#pg-types-record"><em>Postgres.types.record</em></a> reference
documentation for more details on how to use these methods.</p>
<div class="section" id="statement-triggers">
<h3>Statement Triggers<a class="headerlink" href="#statement-triggers" title="Permalink to this headline">¶</a></h3>
<p>Like row-triggers, trigger functions called with statement orientation
are given a <a class="reference internal" href="postgres.html#pg-triggerdata"><em>Postgres.TriggerData</em></a> object as the first argument.
Statement triggers timed after are given one or two additional
<tt class="xref docutils literal"><span class="pre">None</span></tt> objects&#8211;two for update manipulations. These additional
arguments are given in order to accommodate for future functionality.</p>
<p>The following example shows a statement trigger that can handle multiple
event types:</p>
<div class="highlight-python"><pre>CREATE TABLE monitored_table(i int, t text);
CREATE FUNCTION watch_table()
RETURNS TRIGGER LANGUAGE python AS
import Postgres
def after_truncate_statement(td, olds):
       Postgres.notify('was_truncated')
def after_delete_statement(td, olds):
       Postgres.notify('removed_data')
def after_insert_statement(td, news):
       Postgres.notify('new_data')
CREATE TRIGGER watch_trigger BEFORE INSERT ON monitored_table
FOR STATEMENT EXECUTE PROCEDURE watch_table();
LISTEN was_truncated;
LISTEN new_data;
LISTEN removed_data;
INSERT INTO monitored_table VALUES (0, 'text'), (1, 'more text');
DELETE FROM monitored_table;
TRUNCATE monitored_table;</pre>
<p>Statement Trigger Entry Points:</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">before_insert_statement(td):</span></tt></dt>
<dd>Executed before each INSERT statement.</dd>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">before_update_statement(td):</span></tt></dt>
<dd>Executed before each UPDATE statement.</dd>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">before_delete_statement(td):</span></tt></dt>
<dd>Executed before each DELETE statement.</dd>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">before_truncate_statement(td):</span></tt></dt>
<dd>Executed before each TRUNCATE statement.</dd>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">after_insert_statement(td,</span> <span class="pre">news):</span></tt></dt>
<dd><p class="first">Executed after each INSERT statement.</p>
<p class="last">The <tt class="docutils literal"><span class="pre">news</span></tt> argument is always <tt class="xref docutils literal"><span class="pre">None</span></tt>.</p>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">after_update_statement(td,</span> <span class="pre">olds,</span> <span class="pre">news):</span></tt></dt>
<dd><p class="first">Executed after each UPDATE statement.</p>
<p class="last">The <tt class="docutils literal"><span class="pre">olds</span></tt> and <tt class="docutils literal"><span class="pre">news</span></tt> arguments are always <tt class="xref docutils literal"><span class="pre">None</span></tt>.</p>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">after_delete_statement(td,</span> <span class="pre">olds):</span></tt></dt>
<dd><p class="first">Executed after each DELETE statement.</p>
<p class="last">The <tt class="docutils literal"><span class="pre">olds</span></tt> argument is always <tt class="xref docutils literal"><span class="pre">None</span></tt>.</p>
<dt><tt class="docutils literal"><span class="pre">def</span> <span class="pre">after_truncate_statement(td,</span> <span class="pre">olds):</span></tt></dt>
<dd><p class="first">Executed after each TRUNCATE statement.</p>
<p class="last">The <tt class="docutils literal"><span class="pre">olds</span></tt> argument is always <tt class="xref docutils literal"><span class="pre">None</span></tt>.</p>
</div></blockquote>
<div class="section" id="stateful-functions">
<span id="programming-stateful"></span><h2>Stateful Functions<a class="headerlink" href="#stateful-functions" title="Permalink to this headline">¶</a></h2>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Stateful functions are an advanced feature of pg-python.</p>
<p>Stateful functions are entry points decorated with <a class="reference internal" href="postgres.html#pg-stateful"><em>Postgres.Stateful</em></a>.
When an entry point is decorated with <cite>Postgres.Stateful</cite>, the callable
should return a state object. The state object is normally a
generator object, but it can be any object that supports the generator
protocol:</p>
<div class="highlight-python"><pre>CREATE FUNCTION counting() RETURNS int LANGUAGE python AS
from Postgres import Stateful
def main():
    while True:
        yield i
        i = i + 1
SELECT counting(), counting(), col FROM (
 SELECT 'one'::text AS col UNION ALL
 SELECT 'two'::text AS col) AS t(col);
 counting | counting | col
----------+----------+-----
(2 rows)</pre>
<p>The example shows that state is <em>not</em> shared by multiple calls.
Each, independent invocation of the function will have its own state object.
For state shared across all invocations, there is no need to use the
stateful feature, a module global can be referenced and updated as
needed.</p>
<p>Stateful functions get new arguments every time they are invoked; the call
to the <tt class="docutils literal"><span class="pre">send</span></tt> method is given a Python tuple of the new
parameters. With generators, these new parameters are provided by the
<tt class="docutils literal"><span class="pre">yield</span></tt> statement:</p>
<div class="highlight-python"><pre>CREATE FUNCTION counting_and_more(more int) RETURNS int LANGUAGE python AS
It counts, and it adds more.
from Postgres import Stateful
def main(more):
    while True:
        i = i + 1 + more
        args = (yield i)
        more = int(args[0])
SELECT counting_and_more(j), counting_and_more(k) FROM
 (VALUES (0, 1), (0, 1), (1, 10)) AS g(j,k);
-- counting_and_more | counting_and_more
---------------------+-------------------
--(3 rows)</pre>
<p>Stateful can be used with entry points in <a class="reference internal" href="#trigger-returning-functions">Trigger Returning Functions</a> and
<a class="reference internal" href="#set-returning-functions">Set Returning Functions</a>. However, in the case of SRFs, it&#8217;s very unlikely to
offer useful functionality as state is already managed. In the case of TRFs,
it&#8217;s primarily useful in generic triggers where data and routines are
specialized for the target relation can be easily re-used.</p>
<div class="section" id="inline-execution">
<h2>Inline Execution<a class="headerlink" href="#inline-execution" title="Permalink to this headline">¶</a></h2>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">DO-statements were introduced in PostgreSQL 9.0, earlier versions
will not support this feature.</p>
<p>pg-python supports <tt class="docutils literal"><span class="pre">DO</span></tt> statements by executing the code in
a temporary module object. Unlike functions, no entry points are used:</p>
<div class="highlight-python"><pre>CREATE TEMP TABLE tmp (i int, t text);
DO $python$
ins = prepare("INSERT INTO tmp VALUES ($1, $2)")
with xact():
       ins(1, 'A simple sentence.')
       ins(2, 'Another simple sentence.')
$python$ LANGUAGE python;
SELECT * FROM tmp;
-- i |            t
-----+--------------------------
-- 1 | A simple sentence.
-- 2 | Another simple sentence.
--(2 rows)</pre>
<p>Inline execution is very useful for Python as it allows further
initialization of the language&#8217;s environment without a custom function.
In particular, the <cite>Postgres.preload</cite> function can be used from a
DO-statement to make sure that specific procedures are ready for execution
well before they are used.</p>
<div class="section" id="function-modules">
<h2>Function Modules<a class="headerlink" href="#function-modules" title="Permalink to this headline">¶</a></h2>
<p>All <tt class="docutils literal"><span class="pre">python</span></tt> SQL functions are Python modules.
Once loaded, function modules exist in <tt class="docutils literal"><span class="pre">sys.modules</span></tt> as regular modules do.
The primary differences between regular Python modules and function modules
are that the function module&#8217;s source is stored inside the database
and the presence of <tt class="docutils literal"><span class="pre">__func__</span></tt>, a <a class="reference internal" href="postgres.html#pg-function"><em>Postgres.Function</em></a> object, in the module&#8217;s
dictionary. This object represents the Postgres function and provides the
functionality needed to create the module object itself&#8211;it is also the
module&#8217;s <tt class="docutils literal"><span class="pre">__loader__</span></tt>.</p>
<p>A function module&#8217;s attributes:</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">__name__</span></tt></dt>
<dd>The function&#8217;s Oid as a string object. Used as the <tt class="docutils literal"><span class="pre">sys.modules</span></tt> key.</dd>
<dt><tt class="docutils literal"><span class="pre">__loader__</span></tt></dt>
<dd>The module&#8217;s PEP-302 loader.</dd>
<dt><tt class="docutils literal"><span class="pre">__func__</span></tt></dt>
<dd>The module&#8217;s <a class="reference internal" href="postgres.html#pg-function"><em>Postgres.Function</em></a> object.</dd>
<dt><tt class="docutils literal"><span class="pre">__file__</span></tt></dt>
<dd>The qualified regprocedure representation of the SQL function.</dd>
</div></blockquote>
<p>When a function is loaded, all of the necessary information is cached for
re-use. The function module exists in <tt class="docutils literal"><span class="pre">sys.modules</span></tt> and it contains
<tt class="docutils literal"><span class="pre">__func__</span></tt>. That <a class="reference internal" href="postgres.html#pg-function"><em>Postgres.Function</em></a> object contains all the
information needed to properly construct the arguments upon
execution and what to do with the returned object.
However, this information can change. If a function is replaced or a
dependency is modified after the function was loaded, the function needs to
be completely reloaded to reflect these changes. Notably, altering a table
whose type is referenced by a function will cause it to be
reloaded. This can be problematic <em>if</em> the function&#8217;s
module body initializes temporary tables or other session objects for
subsequent use. If a function&#8217;s dependencies are subject to change,
precautions may need to be taken in the module body in order to avoid errors
caused by already initialized objects.</p>
<div class="section" id="data-objects">
<h2>Data Objects<a class="headerlink" href="#data-objects" title="Permalink to this headline">¶</a></h2>
<p>When parameters are given to a function or rows are returned by a prepared
statement, <a class="reference internal" href="postgres.html#pg-object"><em>Postgres.Object</em></a> instances are used to represent the data.
The only exception is that Python&#8217;s <tt class="xref docutils literal"><span class="pre">None</span></tt> is used to represent SQL NULLs.
Some of these data objects have custom interfaces for convenient access to
parts of the data. For example, <a class="reference internal" href="postgres_types.html#pg-types-date"><em>Postgres.types.date</em></a>
objects have properties for getting the day, month, and year parts of the
<p>Data objects can make use of the operators that exist in the database.
Python does not support arbitrary operator methods, so only some
operators are directly accessible from Python. However, the few methods
it does support allows for date-time arithmetic, concatenation, and
comparisons.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Operators are greedy is the sense that they will implicitly convert a
Python object to the type of the other operand before performing the
operation. This only occurs when one of the operands is not
a <a class="reference internal" href="postgres.html#pg-object"><em>Postgres.Object</em></a>.</p>
<p>Data objects are local to the backend context, and most Python modules
are written to expect Python types. Data objects should be converted
to Python types before passing them to most external functions. However,
many Postgres interfaces are capable of taking data objects, so conversion
is not a necessity.
In situations where data objects are not wanted, the <cite>Postgres.pytypes</cite>
decorator and the <cite>Postgres.iterpytypes</cite> processor can be used
to handle many of the common conversion cases. These tools will convert
Postgres integers and strings to Python longs and strings.</p>
<div class="section" id="array-types">
<h3>Array Types<a class="headerlink" href="#array-types" title="Permalink to this headline">¶</a></h3>
<p>Arrays support the standard sequence interfaces provided by many of
the built-in Python types. The notable restriction is that only steps
of one are supported by the <tt class="docutils literal"><span class="pre">__getitem__</span></tt> interface.</p>
<p>Arrays implement a specialized constructor that allows multi-dimension
arrays to be built from balanced Python list objects. The constructor
is very particular about array boundaries and <em>requires</em>
that dimensions be represented using a list object. This requirement is
made to allow the distinction between an array&#8217;s dimension and an element.
By using Python list objects for dimension boundaries, other types&#8211;such as
Python tuples&#8211;can be used to represent the elements of composite arrays:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">Postgres.types</span> <span class="kn">import</span> <span class="n">int4</span>
<span class="n">i4</span> <span class="o">=</span> <span class="n">int4</span><span class="o">.</span><span class="n">Array</span><span class="p">([[</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">],[</span><span class="mi">3</span><span class="p">,</span><span class="mi">4</span><span class="p">]])</span>
<span class="k">assert</span> <span class="n">i4</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span> <span class="o">==</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">i4</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">==</span> <span class="p">(</span><span class="mi">3</span><span class="p">,</span><span class="mi">4</span><span class="p">)</span>
</pre></div>
<p>The Python object interface provided for arrays take on Python semantics.
Index errors will be raised when requested items do not exist, and
a subarray or element will be returned unless a slice is requested.</p>
<p>Postgres arrays are <em>quite</em> different from Python
sequences. While the Python sequence interfaces are implemented to be
consistent with the usual Python semantics, additional array interfaces are
implemented to provide access methods that are consistent with the
interfaces used in SQL:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">Postgres.types</span> <span class="kn">import</span> <span class="n">int4</span>
<span class="n">int4array</span> <span class="o">=</span> <span class="n">int4</span><span class="o">.</span><span class="n">Array</span>
<span class="n">i</span> <span class="o">=</span> <span class="n">int4array</span><span class="p">([</span>
    <span class="p">[[</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">],[</span><span class="mi">3</span><span class="p">,</span><span class="mi">4</span><span class="p">]],</span>
    <span class="p">[[</span><span class="mi">5</span><span class="p">,</span><span class="mi">6</span><span class="p">],[</span><span class="mi">7</span><span class="p">,</span><span class="mi">8</span><span class="p">]],</span>
<span class="p">])</span>
<span class="c"># i[1][1][1] in SQL</span>
<span class="k">assert</span> <span class="n">i</span><span class="o">.</span><span class="n">sql_get_element</span><span class="p">((</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">))</span> <span class="o">==</span> <span class="mi">1</span>
<span class="c"># i[1][1][2] in SQL</span>
<span class="k">assert</span> <span class="n">i</span><span class="o">.</span><span class="n">sql_get_element</span><span class="p">((</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">))</span> <span class="o">==</span> <span class="mi">2</span>
<span class="c"># i[2][1][1] in SQL</span>
<span class="k">assert</span> <span class="n">i</span><span class="o">.</span><span class="n">sql_get_element</span><span class="p">((</span><span class="mi">2</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">))</span> <span class="o">==</span> <span class="mi">5</span>
</pre></div>
<p>The <tt class="docutils literal"><span class="pre">sql_get_element</span></tt> method doesn&#8217;t raise index errors
when the boundary is crossed by an index. It is consistent with how
single elements are accessed using indirection in SQL.
<tt class="xref docutils literal"><span class="pre">None</span></tt> is returned in cases where the requested element
does not exist.</p>
<p>See <a class="reference internal" href="postgres.html#pg-array"><em>Postgres.Array</em></a> for more information about the array interfaces.</p>
<div class="section" id="composite-types">
<h3>Composite Types<a class="headerlink" href="#composite-types" title="Permalink to this headline">¶</a></h3>
<p>Composite types are supported by subclasses of the
<a class="reference internal" href="postgres_types.html#pg-types-record"><em>Postgres.types.record</em></a> type. Composite types are fairly interesting objects
as they are both a sequence and a mapping. In the following examples, the
composite type <tt class="docutils literal"><span class="pre">composite_test</span></tt> will be used to aid examples:</p>
<div class="highlight-python"><pre>CREATE TYPE composite_test AS (i int, t text);</pre>
<p>It can be bound in Python using <tt class="docutils literal"><span class="pre">regtype</span></tt>; subsequent examples will assume
<tt class="docutils literal"><span class="pre">composite</span></tt> to have been bound:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">Postgres</span>
<span class="kn">from</span> <span class="nn">Postgres.types</span> <span class="kn">import</span> <span class="n">regtype</span>
<span class="n">composite</span> <span class="o">=</span> <span class="n">Postgres</span><span class="o">.</span><span class="n">Type</span><span class="p">(</span><span class="n">regtype</span><span class="p">(</span><span class="s">&#39;composite_test&#39;</span><span class="p">))</span>
</pre></div>
<p>Being a sequence and a mapping, records can be created from a sequence or a
dictionary object. When a dictionary is used to provide the fields, absent
attributes will be initialized to NULL. However, when a sequence is given,
all attributes must be present or an exception will be raised:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">from_dict</span> <span class="o">=</span> <span class="n">composite</span><span class="p">({</span><span class="s">&#39;t&#39;</span><span class="p">:</span> <span class="s">&#39;t specified, but no i&#39;</span><span class="p">})</span>
<span class="n">from_seq</span> <span class="o">=</span> <span class="n">composite</span><span class="p">((</span><span class="mi">1234</span><span class="p">,</span> <span class="s">&#39;t&#39;</span><span class="p">))</span>
</pre></div>
<p>The Postgres string representation is also acceptable:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">from_str</span> <span class="o">=</span> <span class="n">composite</span><span class="p">(</span><span class="s">&#39;(1234,&quot;t&quot;)&#39;</span><span class="p">)</span>
</pre></div>
<p>Attributes of the records can be accessed by index or by name using
<tt class="docutils literal"><span class="pre">Postgres.types.record.__getitem__</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">r</span> <span class="o">=</span> <span class="n">composite</span><span class="p">((</span><span class="mi">1234</span><span class="p">,</span> <span class="s">&#39;text&#39;</span><span class="p">))</span>
<span class="k">assert</span> <span class="n">r</span><span class="p">[</span><span class="s">&quot;i&quot;</span><span class="p">]</span> <span class="o">==</span> <span class="n">r</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span> <span class="o">==</span> <span class="mi">1234</span>
<span class="k">assert</span> <span class="n">r</span><span class="p">[</span><span class="s">&quot;t&quot;</span><span class="p">]</span> <span class="o">==</span> <span class="n">r</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">==</span> <span class="s">&#39;text&#39;</span>
</pre></div>
<p>Like Python tuples, records are immutable. So, in order to get a record
with different fields, a new record must be created. This can be achieved
by instantiating the type with the new fields, or by using the record&#8217;s
manipulation methods. There are two manipulation methods, <tt class="docutils literal"><span class="pre">replace</span></tt> and
<tt class="docutils literal"><span class="pre">transform</span></tt>&#8211;both return a new record object:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">x</span> <span class="o">=</span> <span class="n">composite</span><span class="p">((</span><span class="mi">1</span><span class="p">,</span><span class="s">&#39;test&#39;</span><span class="p">))</span>
<span class="c"># the keywords identify the attributes to replace</span>
<span class="n">y</span> <span class="o">=</span> <span class="n">x</span><span class="o">.</span><span class="n">replace</span><span class="p">(</span><span class="n">t</span> <span class="o">=</span> <span class="s">&#39;newval&#39;</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">y</span> <span class="o">==</span> <span class="n">ctype</span><span class="p">((</span><span class="mi">1</span><span class="p">,</span><span class="s">&#39;newval&#39;</span><span class="p">))</span>
</pre></div>
<div class="admonition tip">
<p class="first admonition-title">Tip</p>
<p class="last">These methods are particularly useful with <cite>Trigger Returning Functions</cite>.</p>
<p>See <a class="reference internal" href="postgres_types.html#pg-types-record"><em>Postgres.types.record</em></a> for more information.</p>
<div class="section" id="database-access">
<h2>Database Access<a class="headerlink" href="#database-access" title="Permalink to this headline">¶</a></h2>
<p>Database access is provided by <a class="reference internal" href="postgres.html#pg-statement"><em>Postgres.Statement</em></a> objects. Each created
instance represents a single SQL statement that can take positional
parameters and return rows.</p>
<p>Statement objects have multiple methods that can execute the statement. The
simplest form of execution is performed by calling a statement
object(__call__). When this execution method is used, the entire result set
is returned as a Python list:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">Postgres</span>
<span class="c"># prepare is a built-in pointing at Postgres.Statement</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">prepare</span><span class="p">(</span><span class="s">&quot;SELECT i FROM (VALUES (1), (2), (3)) AS g(i) WHERE i &gt; $1&quot;</span><span class="p">)</span>
<span class="c"># positional parameters are given as arguments</span>
<span class="n">r</span> <span class="o">=</span> <span class="n">stmt</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
<span class="n">Postgres</span><span class="o">.</span><span class="n">WARNING</span><span class="p">(</span><span class="nb">str</span><span class="p">(</span><span class="nb">list</span><span class="p">(</span><span class="nb">map</span><span class="p">(</span><span class="nb">tuple</span><span class="p">,</span> <span class="n">r</span><span class="p">))))</span>
<span class="c"># WARNING:  [(int4(&#39;2&#39;),), (int4(&#39;3&#39;),)]</span>
<span class="c"># CONTEXT:  [__inline_executor__ while executing main]</span>
</pre></div>
<p>Internally, a cursor is allocated when <tt class="docutils literal"><span class="pre">__call__</span></tt> is used,
but all the rows are fetched and stored in the list. Most
execution methods will return an actual <a class="reference internal" href="postgres.html#pg-cursor"><em>Postgres.Cursor</em></a> object.
The cursor that&#8217;s created will be configured to suite the request. When the <tt class="docutils literal"><span class="pre">rows</span></tt>
execution method is used, the cursor returned can only be used as an
iterable producing individual rows&#8211;the seek and read methods are not
usable:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">Postgres</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">prepare</span><span class="p">(</span><span class="s">&quot;SELECT 1&quot;</span><span class="p">)</span>
<span class="k">for</span> <span class="n">x</span> <span class="ow">in</span> <span class="n">stmt</span><span class="o">.</span><span class="n">rows</span><span class="p">():</span>
    <span class="n">Postgres</span><span class="o">.</span><span class="n">WARNING</span><span class="p">(</span><span class="nb">str</span><span class="p">(</span><span class="n">x</span><span class="p">[</span><span class="mi">0</span><span class="p">]))</span>
</pre></div>
<p>The <tt class="docutils literal"><span class="pre">rows</span></tt> execution method should be used when the result
set is being processed on a row-by-row basis. When a large result set is
being produced it is particularly advantageous in comparison to
<tt class="docutils literal"><span class="pre">__call__</span></tt> as the result set is not allocated in a Python
<p>Scrollable cursors can be created using the <tt class="docutils literal"><span class="pre">declare(...)</span></tt>
execution method. This will return a cursor whose seek and read methods are
usable. The cursor&#8217;s <tt class="docutils literal"><span class="pre">__next__</span></tt> method is usable, but in
order to guarantee exact positioning, no read-ahead is performed:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">Postgres</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">prepare</span><span class="p">(</span><span class="s">&quot;SELECT i FROM generate_series($1, $2) AS g(i)&quot;</span><span class="p">)</span>
<span class="n">cur</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">declare</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">100</span><span class="p">)</span>
<span class="n">first10</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">read</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span>
<span class="n">cur</span><span class="o">.</span><span class="n">seek</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span>
<span class="n">last50</span> <span class="o">=</span> <span class="n">cur</span><span class="o">.</span><span class="n">read</span><span class="p">()</span>
</pre></div>
<p>Manipulation statements without a RETURNING clause do not return rows.
Calling methods that return a cursor configured to return rows with such
statements will result in an exception. When these kinds of statements
need to be executed, <tt class="docutils literal"><span class="pre">__call__</span></tt> and <tt class="docutils literal"><span class="pre">first</span></tt>
should be used. In the case of <tt class="docutils literal"><span class="pre">first</span></tt>, only the row count
is returned. For <tt class="docutils literal"><span class="pre">__call__</span></tt>, a tuple containing the command
tag and the row count is returned:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">Postgres</span>
<span class="n">sqlexec</span><span class="p">(</span><span class="s">&quot;CREATE TEMP TABLE simple_data (t text, i int)&quot;</span><span class="p">)</span>
<span class="n">ins</span> <span class="o">=</span> <span class="n">prepare</span><span class="p">(</span><span class="s">&quot;INSERT INTO simple_data (t,i) VALUES ($1, $2)&quot;</span><span class="p">)</span>
<span class="c"># __call__</span>
<span class="n">cmdtag</span><span class="p">,</span> <span class="n">rowcount</span> <span class="o">=</span> <span class="n">ins</span><span class="p">(</span><span class="s">&#39;text&#39;</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span>
<span class="c"># first</span>
<span class="n">rowcount_from_first</span> <span class="o">=</span> <span class="n">ins</span><span class="o">.</span><span class="n">first</span><span class="p">(</span><span class="s">&#39;other text&#39;</span><span class="p">,</span> <span class="mi">2</span><span class="p">)</span>
<span class="n">Postgres</span><span class="o">.</span><span class="n">WARNING</span><span class="p">(</span><span class="s">&quot;</span><span class="si">%s</span><span class="s"> </span><span class="si">%d</span><span class="s"> rows&quot;</span> <span class="o">%</span><span class="p">(</span><span class="n">cmdtag</span><span class="p">,</span> <span class="n">rowcount</span><span class="p">))</span>
</pre></div>
<p>When DML statements need to be executed for a sequence of parameters, the
<tt class="docutils literal"><span class="pre">load_rows</span></tt> and <tt class="docutils literal"><span class="pre">load_chunks</span></tt> methods
can be used. These methods take an iterable that ultimately produces
the arguments to execute the statement with. Upon exhausting the given
iterator, these methods will return the total row count. Notably, these
methods <em>cannot</em> be used with statements that return
rows&#8211;an exception will be raised by either method if the statement
returns rows. An example of <tt class="docutils literal"><span class="pre">load_rows</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">Postgres</span>
<span class="n">sqlexec</span><span class="p">(</span><span class="s">&quot;CREATE TEMP TABLE load_rows_test (t text, i int)&quot;</span><span class="p">)</span>
<span class="n">ins</span> <span class="o">=</span> <span class="n">prepare</span><span class="p">(</span><span class="s">&quot;INSERT INTO load_rows_test (t,i) VALUES ($1, $2)&quot;</span><span class="p">)</span>
<span class="c"># __call__</span>
<span class="n">count</span> <span class="o">=</span> <span class="n">ins</span><span class="o">.</span><span class="n">load_rows</span><span class="p">([</span>
       <span class="p">(</span><span class="s">&#39;first&#39;</span><span class="p">,</span> <span class="mi">1</span><span class="p">),</span>
       <span class="p">(</span><span class="s">&#39;second&#39;</span><span class="p">,</span> <span class="mi">2</span><span class="p">),</span>
       <span class="p">(</span><span class="s">&#39;third&#39;</span><span class="p">,</span> <span class="mi">3</span><span class="p">),</span>
<span class="p">])</span>
<span class="n">Postgres</span><span class="o">.</span><span class="n">WARNING</span><span class="p">(</span><span class="s">&quot;</span><span class="si">%d</span><span class="s"> rows&quot;</span> <span class="o">%</span><span class="p">(</span><span class="n">count</span><span class="p">,))</span>
<span class="c"># WARNING:  3 rows</span>
<span class="c"># CONTEXT:  [__inline_executor__ while executing main]</span>
</pre></div>
<p>Contents of the temporary table:</p>
<div class="highlight-python"><pre>SELECT * FROM load_rows_test;
--   t    | i
----------+---
-- first  | 1
-- second | 2
-- third  | 3
--(3 rows)</pre>
<p>See <a class="reference internal" href="postgres.html#pg-statement"><em>Postgres.Statement</em></a> and <a class="reference internal" href="postgres.html#pg-cursor"><em>Postgres.Cursor</em></a> for further information about
the database access interfaces.</p>
<div class="section" id="error-control">
<span id="errors"></span><h2>Error Control<a class="headerlink" href="#error-control" title="Permalink to this headline">¶</a></h2>
<p>When a database error is caused by use of a Postgres interface, a
<a class="reference internal" href="postgres.html#pg-exception"><em>Postgres.Exception</em></a> is raised. After a database error occurs, access to the
database is prohibited until the failed
transaction state is corrected. The transaction state cannot be
corrected within the function unless the error occurs inside a
<a class="reference internal" href="postgres.html#pg-transaction"><em>Postgres.Transaction</em></a> block:</p>
<div class="highlight-python"><pre>CREATE TEMP TABLE pkviolation (s SERIAL PRIMARY KEY);
INSERT INTO pkviolation VALUES (DEFAULT);
DO $python$
import Postgres
insert_stmt = prepare("INSERT INTO pkviolation VALUES ($1)")
    with xact():
        insert_stmt(1)
except Postgres.Exception as x:
    if x.code != '23505':
        raise
    Postgres.WARNING("caught unique violation")
    insert_stmt(12)
$python$ LANGUAGE python;
SELECT * FROM pkviolation;
--(2 rows)</pre>
<p><a class="reference internal" href="postgres.html#pg-transaction"><em>Postgres.Transaction</em></a> context managers can only be used once, so
<tt class="docutils literal"><span class="pre">xact</span></tt> should normally be directly invoked by the with-statement,
<tt class="docutils literal"><span class="pre">with</span> <span class="pre">xact():</span></tt>.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">It is important that exceptions are caught <em>outside</em> of
the transaction block. <em>Attempting to interact with the database</em>
<em>while in a failed transaction will result in another exception being raised.</em></p>
<p>Postgres errors can be raised from Python using <a class="reference internal" href="postgres.html#pg-ereport"><em>Postgres.ereport</em></a>.
However, the <a class="reference internal" href="postgres.html#pg-error"><em>Postgres.ereport</em></a> wrapper is more convenient, so direct use
of <tt class="docutils literal"><span class="pre">ereport</span></tt> is <em>not</em> recommended. This
can be used to construct custom errors with more useful information than the
more verbose Python exception output:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">Postgres</span>
<span class="n">Postgres</span><span class="o">.</span><span class="n">ERROR</span><span class="p">(</span>
    <span class="n">message</span> <span class="o">=</span> <span class="s">&#39;application error message&#39;</span><span class="p">,</span>
    <span class="n">code</span> <span class="o">=</span> <span class="s">&#39;AP001&#39;</span><span class="p">,</span>
    <span class="c"># Don&#39;t include the usual PL context(traceback).</span>
    <span class="n">inhibit_pl_context</span> <span class="o">=</span> <span class="bp">True</span><span class="p">,</span>
<span class="p">)</span>
</pre></div>
<p>By default, all exceptions include the traceback and additional context
identifying the Postgres function that raised the exception. The
<tt class="docutils literal"><span class="pre">ereport</span></tt> interface allows this information to be
suppressed in order to provide the programmer with greater control over the
reported error message.</p>
          </div>
        </div>
      </div>
      <div class="sphinxsidebar">
        <div class="sphinxsidebarwrapper">
  <h3><a href="index.html">Table Of Contents</a></h3>
<li><a class="reference internal" href="#">Programming</a><ul>
<li><a class="reference internal" href="#builtins">Builtins</a></li>
<li><a class="reference internal" href="#normal-functions">Normal Functions</a></li>
<li><a class="reference internal" href="#set-returning-functions">Set Returning Functions</a></li>
<li><a class="reference internal" href="#trigger-returning-functions">Trigger Returning Functions</a><ul>
<li><a class="reference internal" href="#row-triggers">Row Triggers</a><ul>
<li><a class="reference internal" href="#suppressing-manipulations">Suppressing Manipulations</a></li>
<li><a class="reference internal" href="#substituting-rows">Substituting Rows</a></li>
<li><a class="reference internal" href="#statement-triggers">Statement Triggers</a></li>
<li><a class="reference internal" href="#stateful-functions">Stateful Functions</a></li>
<li><a class="reference internal" href="#inline-execution">Inline Execution</a></li>
<li><a class="reference internal" href="#function-modules">Function Modules</a></li>
<li><a class="reference internal" href="#data-objects">Data Objects</a><ul>
<li><a class="reference internal" href="#array-types">Array Types</a></li>
<li><a class="reference internal" href="#composite-types">Composite Types</a></li>
<li><a class="reference internal" href="#database-access">Database Access</a></li>
<li><a class="reference internal" href="#error-control">Error Control</a></li>
  <h4>Previous topic</h4>
  <p class="topless"><a href="admin.html"
                        title="previous chapter">Requirements</a></p>
  <h4>Next topic</h4>
  <p class="topless"><a href="gotchas.html"
                        title="next chapter">Gotchas</a></p>
  <h3>This Page</h3>
  <ul class="this-page-menu">
    <li><a href="_sources/programming.txt"
           rel="nofollow">Show Source</a></li>
<div id="searchbox" style="display: none">
  <h3>Quick search</h3>
    <form class="search" action="search.html" method="get">
      <input type="text" name="q" size="18" />
      <input type="submit" value="Go" />
      <input type="hidden" name="check_keywords" value="yes" />
      <input type="hidden" name="area" value="default" />
    </form>
    <p class="searchtip" style="font-size: 90%">
    Enter search terms or a module, class or function name.
<script type="text/javascript">$('#searchbox').show(0);</script>
        </div>
      </div>
      <div class="clearer"></div>
    <div class="related">
      <h3>Navigation</h3>
        <li class="right" style="margin-right: 10px">
          <a href="genindex.html" title="General Index"
             >index</a></li>
        <li class="right" >
          <a href="gotchas.html" title="Gotchas"
             >next</a> |</li>
        <li class="right" >
          <a href="admin.html" title="Requirements"
             >previous</a> |</li>
        <li><a href="index.html">pg-python v1.0.1 documentation</a> &raquo;</li> 
      </ul>
    <div class="footer">
        &copy; Copyright 2010, James William Pye &lt;x@jwp.name&gt;.
      Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0.7.
Navigation Menu

Uh oh!

Uh oh!

FilesExpand file tree

programming.html

Latest commit

History

programming.html

File metadata and controls
