PostgreSQL では手続き型言語(PL=Procedural Language)を使って定義する際に、PL/pgSQL 以外にも Pl/Tcl, PL/Perl, PL/Python などさまざまな言語を使えると知ったので、PL/Python を触ってみた。


関数の引数に文字列を渡すと twitter の検索結果を返す関数を作成



  • OS : Ubuntu 12.04, 64-bit
  • PostgreSQL : 9.1
  • Python : 2.7
  • requests : 0.8.2(apt-get では←のバージョンだったので)$ apt-get install python-requests する


まずは PL/Python を使えるようにする。

インストール済み PL 言語は createlang コマンドで確認する

$ createlang --list test
Procedural Languages
  Name   | Trusted?
 plpgsql | yes

デフォルトでは PL 系の中では plpgsql だけが有効になっている。
apt-get でインストール可能な PL/Python をチェック

$ apt-cache search plpython
postgresql-plpython-9.1 - PL/Python procedural language for PostgreSQL 9.1
postgresql-plpython3-9.1 - PL/Python 3 procedural language for PostgreSQL 9.1
postgresql-plpython-8.4 - PL/Python procedural language for PostgreSQL 8.4

PostgreSQL 9.1 に 2.*系 Python をインストールしたいので postgresql-plpython-9.1 をインストール。

$ sudo apt-get install postgresql-plpython-9.1

次に利用したいデータベースに PL/Python をインストール。createlang でインストール先データベース名とともに指定。

$ createlang plpythonu test
$ createlang --list test
 Procedural Languages
   Name    | Trusted?
 plpgsql   | yes
 plpythonu | no
$ psql test
test=# SELECT * FROM pg_available_extensions;
    name    | default_version | installed_version |                  comment
 plpythonu  | 1.0             | 1.0               | PL/PythonU untrusted procedural language
 plpgsql    | 1.0             | 1.0               | PL/pgSQL procedural language
(2 rows)

plpythonu が trusted=no で追加された。

Python2 と Python3 を明示的に区別したい場合は $ createlang plpython2u ...$ createlang plpython3u ... のようにする。$ createlang plpythonu ... とすると、先に見つかった Python が採用される。

Chapter 42.1. PL/Python – Python Procedural Language – Python 2 vs. Python 3


関数は CREATE FUNCTION のシンタックスの $$ ブロック内に Python のコードをベチャッと書けばOK。

CREATE FUNCTION funcname (argument-list)
  RETURNS return-type
AS $$
  # PL/Python function body
$$ LANGUAGE plpythonu;

Python の requests(0.8.4) モジュールを使って twitter に検索を投げて本文を返す関数は以下。(PL/Python はplpython2u を利用)

CREATE OR REPLACE FUNCTION twitter(query text)
AS $$
import json
import urllib
import requests
url = ''
response = requests.get(url%urllib.quote(query))
response = json.loads(response.content)
plpy.warning(response['completed_in']) # logging
for tweet in response['results']:
    yield tweet['text']
$$ LANGUAGE plpython2u;

PL/Python には plpy という名前の ユーティリティーモジュールも用意されている。上の例では検索時間をログ出力(plpy.warning)している。


作成した関数を psql から確認するには \df コマンドを利用する。

test=# \df
                         List of functions
 Schema |  Name   | Result data type | Argument data types |  Type
 public | twitter | SETOF text       | query text          | normal
(1 row)


作成した関数を利用して Twitter に問い合わせる

#postgresql のハッシュタグで検索

test=# select twitter('#postgresql');
WARNING:  0.017
CONTEXT:  PL/Python function "twitter"
 年末年始休暇の宿題だった、PostgreSQL用の日本語正規化テキスト型 ntext のプロトタイプができた。 #postgresql
 RT @al3xandru: ♻ PostgreSQL GiST and GIN Index Types #PostgreSQL
 ♻ PostgreSQL GiST and GIN Index Types #PostgreSQL
 rsync-ing 40Gb of #postgresql databases takes longer than expected...
 pldebugger / pdbgapi en #PostgreSQL 9.2.2 - Fedora 17 #spanish #español
(5 rows)


test=# select twitter('鷹狩');
WARNING:  0.016
CONTEXT:  PL/Python function "twitter"
 @26ap6 お!イイネ!\(^o^)/小鷹狩さんで始まろう!
 朝の散歩終了をー 今日は僕の散歩エリアに鷹狩の方々が来ていたよ 鳥さんみんな逃げておー 写真は今日のではありませんが 白菜畑の白菜の上のカラスくん(さんかも?)です
(5 rows)


PL 系関数はオーバーロードできるので、関数名だけでなく引数も含めてどの関数を削除するのか指定する

test=# drop function twitter(query text);

trusted/untrusted PL

PL系言語は大きく分けて trusted(pgSQL, Tcl, Perl)untrusted(TclU, PythonU) の2種類がある。(最後の ‘u’ は ‘untrusted’ を指している。)
trusted 系は安全に PL を実行するように、ファイル・システムの操作など、セキュリティ上問題のありそうな操作は行えないようになっている。
一方で untrusted 系にはそのような制約はない。

Chapter 38.1. Procedural Languages – Installing Procedural Languages

The optional key word TRUSTED specifies that the language does not grant access to data that the user would not otherwise have. Trusted languages are designed for ordinary database users (those without superuser privilege) and allows them to safely create functions and trigger procedures. Since PL functions are executed inside the database server, the TRUSTED flag should only be given for languages that do not allow access to database server internals or the file system. The languages PL/pgSQL, PL/Tcl, and PL/Perl are considered trusted; the languages PL/TclU, PL/PerlU, and PL/PythonU are designed to provide unlimited functionality and should not be marked trusted.

Chapter 42. PL/Python – Python Procedural Language

As of PostgreSQL 7.4, PL/Python is only available as an “untrusted” language, meaning it does not offer any way of restricting what users can do in it. It has therefore been renamed to plpythonu. The trusted variant plpython might become available again in future, if a new secure execution mechanism is developed in Python. The writer of a function in untrusted PL/Python must take care that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator. Only superusers can create functions in untrusted languages such as plpythonu.

例えば trusted な PL/Perl でファイル操作を行う関数を定義しようとすると、エラーが発生する。

CREATE FUNCTION badfunc() RETURNS integer AS $$
    my $tmpfile = "/tmp/badfile";
    open my $fh, '>', $tmpfile
        or elog(ERROR, qq{could not open the file "$tmpfile": $!});
    print $fh "Testing writing to a file\n";
    close $fh or elog(ERROR, qq{could not close the file "$tmpfile": $!});
    return 1;
$$ LANGUAGE plperl;

ERROR:  'open' trapped by operation mask at line 4.
CONTEXT:  compilation of PL/Perl function "badfunc"

PL/Python で定義した関数は PostgreSQL のサーバサイドの名前空間で定義される。
例えば、再帰呼び出しをするような場合は、関数を PostgreSQL サーバに問い合わせる必要がある。

RETURNS integer
AS $$
if n <= 1:
    return n
    return n * plpy.execute("select fact(%d) as n" % (n-1))[0]["n"]
$$ LANGUAGE plpython2u;

test=# select fact(5);
(1 row)


Tagged with: , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: