PL/v8はJavaScriptをPostgreSQLの組み込み言語に追加する拡張ですが、trustedというセキュリティ的な位置づけから、ウェブをはじめ外部リソースへアクセスできません。でも他の組み込み言語でウェブアクセス用の関数を作って連携させれば、例えばWebAPIで得たデータをPL/v8に渡すことが可能に。そういうデータとJavaScriptは相性が良さそう、かつPL/v8の豊富な機能（Typed Array、ウィンドウ関数作成）も有効活用できるかも、という目論見で作業を始めました。とりあえず5回に分けて最初の試みを書きます。

（2）PL/Pythonで作ったウェブアクセス関数を、PL/v8から使う（1月5日）（3）WebAPIの例として、MediaWiki APIからデータを取得（1月7日）（4）関数の改良（ⅰ）JSONを処理してpageidを直接返す（1月8日）（5）関数の改良（ⅱ）複数行に対するウェブアクセスを、ウィンドウ関数で1回にまとめる（1月9日、終）

Contents

実行環境

Windows 7 64bit + PostgreSQL 9.4.5（zip版からバッチファイルで起動）
Python 3.3.5 + PL/Python3
PL/v8 1.4.2
コマンドプロンプトの代わりにConEmu 151208 + psql
PL/Pythonを使うのでPostgreSQLはスーパーユーザ権限が必要

PostgreSQL用の汎用ウェブアクセス関数を、PL/Pythonで作成

まず今回の作業用データベースを適当な名前で作成し、PL/PythonとPL/v8を有効化します。例えばpsqlでは ↓ こんな風に。PL/Pythonの拡張名をplpython3uとするのが忘れやすいかも。

-- on psql
create database test_webapi;
\c test_webapi
create extension plpython3u;
create extension plv8;

↓ 現状の確認。psqlは昨日書いた環境設定込みのバッチファイルで起動したので、デフォルトと少し違う外見です。

select current_database();
+------------------+
| current_database |
+------------------+
| test_webapi      |
+------------------+
(1 row)

select extname, extversion from pg_extension;
+------------+------------+
|  extname   | extversion |
+------------+------------+
| plpgsql    | 1.0        |
| plv8       | 1.4.2      |
| plpython3u | 1.0        |
+------------+------------+
(3 rows)

Pythonでウェブアクセスする方法はいろいろあると思いますが、今回は標準で使えるurllibパッケージを使用。ストアドの定義文は ↓ こんな感じ。結果をデコードせずそのままバイナリで返します。その理由は後述。

create or replace function plpy_urlopen(url text)
returns bytea language plpython3u immutable as
$$
    from urllib.request import urlopen
    from urllib.error import URLError
    try:
        response = urlopen(url)
    except ValueError as e:
        plpy.notice('ValueError: unknown url type')
    except URLError as e:
        plpy.notice('URLError or HTTPError. failed to reach a server')
    else:
        return(response.read());
$$;

↓urllibパッケージのドキュメントと、エラー処理で参考にしたウェブページです。

↓ ストアドの使い方は簡単で、引数にURLを入れてSELECTするのが基本。ただしバイナリが返るので、HTMLなどテキストをリクエストする際はクエリ側で文字コードを指定して変換します。

select plpy_urlopen('アクセス先のURL');
    -- bytea型が返る
    
select convert_from(plpy_urlopen('アクセス先のURL'), '文字コード');
    -- HTMLやWebAPIの場合、クエリ側で文字コードを指定しテキストにする

テキストの場合はクエリ側の手間が増えますが、一つのストアドでテキスト・バイナリ両方のウェブアクセスに使え、文字コードが不明なテキストデータも「とりあえず」バイナリで保存しておく等の対処が可能。このような汎用性・柔軟性を考えてbytea型で返すことにしました。

作った関数のテスト

↓ 本ブログのトップにアクセスし、結果のbytea型をそのまま出力したところ（書式はhex）。

select plpy_urlopen('http://kenpg.bitbucket.org/');

plpy_urlopen                                                                         
\x3c21444f43545950452068746d6c3e3c68746d6c3e0d0a3c686561643e0d0a3c6d
65746120687474702d65717569763d22436f6e74656e742d547970652220636f6e74
656e743d22746578742f68746d6c3b20636861727365743d5554462d38223e0d0a3c
6d65746120687474702d65717569763d22582d55412d436f6d70617469626c652220
636f6e74656e743d2249453d3130222f3e0d0a3c2f686561643e0d0a3c626f64793e
3c736372697074207372633d226b656e70675f766572315f696e6465782e6a732220
747970653d22746578742f6a617661736372697074223e3c2f7363726970743e3c2f
626f64793e0d0a3c2f68746d6c3e0d0a
(1 row)

↓ 同じ結果を、クエリ側で文字コード（ここではUTF-8）を指定してテキストに戻す例。

select convert_from(
    plpy_urlopen('http://kenpg.bitbucket.org/'), 'utf8');

convert_from
<!DOCTYPE html><html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=10"/>charset=UTF-8">
</head>
<body><script src="kenpg_ver1_index.js" type="text/javascript"></script></body>
</html>
(1 row)

↓Shift JISの日本語サイト（例: デイリーポータルZ）も正常に変換されました。

select convert_from(
    plpy_urlopen('http://portal.nifty.com/'), 'sjis');

↓ 文字コードEUC-JPのサイトは探すのに苦労したけど、サンゴー企画さんがありました。こちらの結果もOK。

select convert_from(
    plpy_urlopen('http://sango.edisc.jp/'), 'eucjp');

↓ エラー処理のテスト。サーバが存在しないとかネットワーク非接続の場合はURLErrorで、URLの形式が不正ならValueErrorでキャッチ。どちらもエラーメッセージをplpy.notice()関数で出力し、ストアド自体はNullを返して正常終了します。これで他のクエリやストアドへの組み込みが楽。URLやネットワークのエラーで止まらないので。

-- 存在しないアドレス
select plpy_urlopen('https://kenpg.org/');
NOTICE:  URLError or HTTPError. failed to reach a server
CONTEXT:  PL/Python function "plpy_urlopen"
+--------------+
| plpy_urlopen |
+--------------+
|              |
+--------------+
(1 row)

-- ネットワークに非接続の場合も、上と同じ結果になる


-- 引数がURL形式でない
select plpy_urlopen('httpkenpg.org/');
NOTICE:  ValueError: unknown url type
CONTEXT:  PL/Python function "plpy_urlopen"
+--------------+
| plpy_urlopen |
+--------------+
|              |
+--------------+
(1 row)

↓ テストの最後。ウェブ上のPNG画像を取得し、一昨日書いたCOPY ... TO PROGRAM + xxdのクエリでバイナリファイルに保存しました。

-- bytea型の表示形式を確認
select current_setting('bytea_output');
+-----------------+
| current_setting |
+-----------------+
| hex             |
+-----------------+
(1 row)

-- hexならencode()不要でcopy -> xxdでバイナリファイルにできる
copy (
    select plpy_urlopen('http://kenpg.bitbucket.org/blog/201601/02/2.png')
) to program
'"D:/AppsPortable/GitPortable/2.6.2/App/Git/usr/bin/xxd" -r -p > "R:/plpy_test.png"';

今回の主眼はWebAPIなのでしばらくテキストの取得のみ考える予定ですが、一応バイナリも含めて単純・汎用的なウェブアクセス関数ができました。明日以降はPL/v8での作業に移り、この関数を呼び出してWebAPIデータ（主にJSON形式）を扱う方法について書きます。

実行環境

関連リンクと過去記事

PostgreSQL用の汎用ウェブアクセス関数を、PL/Pythonで作成

作った関数のテスト