Database Reference
In-Depth Information
Example 8-13. Using Python function in a query
SELECT
search_term
,
left
(
postgresql_help_search
(
search_term
),
125
)
As
result
FROM
(
VALUES
(
'regexp_match'
),(
'pg_trgm'
),(
'tsvector'
))
As
x
(
search_term
);
Recall that PL/Python is an untrusted language, without a trusted counterpart. This
means only superusers can write functions using PL/Python, and the function can in‐
teract with the file system of the OS.
Example 8-14
takes advantage of the untrusted
nature of PL/Python to retrieve file listings from a directory. Keep in mind that from
the perspective of the OS, a PL/Python function runs under the context of the
post
gres
user account created during installation, so you need to be sure that this account
has adequate access to the relevant directories.
Example 8-14. List files in directories
CREATE
OR
REPLACE
FUNCTION
list_incoming_files
()
RETURNS
SETOF
text
AS
$$
import
os
return
os
.
listdir
(
'/incoming'
)
$$
LANGUAGE
'plpython2u'
VOLATILE
SECURITY
DEFINER
;
Run the function in
Example 8-14
with the following query:
SELECT
filename
FROM
list_incoming_files
()
As
filename
WHERE
filename
ILIKE
'%.csv'
Writing PL/V8, PL/CoffeeScript, and PL/LiveScript
Functions
allows you to write functions in JavaScript and interface with the JSON data type. It is
not part of the core PostgreSQL offering, so you won't find it in most popular Post‐
greSQL distributions except Heroku. You can always compile it from source. For Win‐
dows, we've built
PL/V8 extension windows binaries
.
You can download them from our
Postgres OnLine site for
PostgreSQL 9.2
, and from our Postgres OnLine site for
Post‐
greSQL 9.3
(both 32-bit and 64-bit).
Although you can compile PL/V8 version 9.1, we strongly suggest that you upgrade to
9.2, with native JSON support, instead.
When you add PL/V8 binaries to your PostgreSQL setup, you get not one, but three
JavaScript-related languages:
PL/V8 (plv8)
This is the basic language that serves as the basis for the other two JavaScript lan‐
guages.
