File: plpython-database.html

package info (click to toggle)
pgadmin3 1.4.3-2
  • links: PTS
  • area: main
  • in suites: etch, etch-m68k
  • size: 29,796 kB
  • ctags: 10,758
  • sloc: cpp: 55,356; sh: 6,164; ansic: 1,520; makefile: 576; sql: 482; xml: 100; perl: 18
file content (104 lines) | stat: -rw-r--r-- 6,029 bytes parent folder | download
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, &#8220;PL/Python Functions&#8221;</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>