Database Reference
In-Depth Information
The extensions are compiled against a specific minor version of Python. You should
install the minor version of Python that matches what your
plpythonu
extensions were
compiled against. For example, if your
plpython2u
was compiled against Python 2.7,
you should install Python 2.7.
Basic Python Function
PostgreSQL automatically converts PostgreSQL data types to Python data types and
back. PL/Python is capable of returning arrays and composite types. You can use PL/
Python to write triggers and create aggregate functions. We've demonstrated some of
these in the Postgres OnLine Journal, in
PL/Python Examples
.
Python allows you to perform feats that aren't possible in PL/pgSQL. In
Example 8-12
,
we demonstrate how to write a PL/Python function to do a text search of the online
PostgreSQL document site.
Example 8-12. Searching PostgreSQL documents using PL/Python
CREATE
OR
REPLACE
FUNCTION
postgresql_help_search
(
param_search
text
)
RETURNS
text
AS
$$
import
urllib
,
re
response
=
urllib
.
urlopen
(
'http://www.postgresql.org/search/?u=%2Fdocs%2Fcurrent%2F&q='
+
param_search
)
raw_html
=
response
.
read
()
result
=
raw_html
[
raw_html
.
find
(
"<!-- docbot goes here -->"
)
:
raw_html
.
find
(
"<!--
pgContentWrap -->"
)
-
1
]
result
=
re
.
sub
(
'<[^<]+?>'
,
''
,
result
).
strip
()
return
result
$$
LANGUAGE
plpython2u
SECURITY
DEFINER
STABLE
;
Imports the libraries we'll be using.
Performs a search after concatenating the search term.
Reads the response and saves the retrieved HTML to a variable called
raw_html
.
Saves the part of the
raw_html
that starts with
<!-- docbot goes here -->
and
ends just before the beginning of
<!-- pgContentWrap -->
into a new variable
called
result
.
Removes leading and trailing HTML symbols and whitespace.
Returns
result
.
Calling Python functions is no different from calling functions written in other lan‐
guages. In
Example 8-13
, we use the function we created in
Example 8-12
to output the
result with three search terms.
