PostgreSQL の 手続き型言語 PL/pgSQL と PL/Pythonをつかってみた
こんにちは、みかみです。
常日頃、Redshift をさわってます。以前は MySQL ばかりさわってました（LAMP世代です。。
PostgreSQL はさわったことがありませんでした。
MySQL と Oracle でストアドプロシージャはつくったことがありましたが、ユーザー定義関数（UDF）は未経験でした。
はじめに
やりたいこと
- PostgreSQL の手続き型言語( PL ) PL/pgSQL を書いてみたい
- ユーザー定義関数（UDF）をつくってみたい
- PostgreSQL×Python の PL、PL/Python をさわってみたい
確認環境
- OS：Windows10（Mac VMware Fusion）
- Python：3.6.0
- PostgreSQL：9.6.1
確認用データ
データカタログサイトから、最高気温データをもらってきて、必要なカラムだけ抽出した CSV ファイルを作成します。
import pandas as pd
file_name = 'temperature.csv'
df = pd.read_csv('mxtemsadext00_rct.csv')
df.loc[:,['観測所番号','都道府県','地点','今年の最高気温（℃)（昨日まで）','昨日までの観測史上1位の値（℃）']].to_csv('temp.csv', index=False, header=False)
できた CSV ファイルがこちら。
11001,北海道宗谷地方,宗谷岬,21.1,31.9 11016,北海道宗谷地方,稚内,20.9,31.3 11046,北海道宗谷地方,礼文,17.7,28.4 （中略） 88986,鹿児島県,与論島,28.3,34.5 91011,沖縄県,伊是名,30.9,35.6 91021,沖縄県,奥,29.1,33.9 91096,沖縄県,粟国,29.7,35.3 91107,沖縄県,名護,29.3,35.1 91141,沖縄県,北原,29.6,34.6 91146,沖縄県,久米島,30.3,34.8 91166,沖縄県,宮城島,28.8,34.7 91181,沖縄県,渡嘉敷,28.0,32.5 91197,沖縄県,那覇,29.7,35.6 91216,沖縄県,慶良間,29.4,33.5 91236,沖縄県,安次嶺,29.3,35.0 91241,沖縄県,糸数,28.0,36.1 92006,沖縄県,北大東,29.0,34.1 92011,沖縄県,南大東(南大東島),29.3,35.3 92012,沖縄県,旧東,29.4,33.5 93012,沖縄県,下地,29.9,36.1 93041,沖縄県,宮古島,30.8,35.3 93042,沖縄県,鏡原,30.3,35.2 93062,沖縄県,仲筋,30.5,35.4 94001,沖縄県,伊原間,30.9,36.1 94011,沖縄県,所野,30.2,34.7 94017,沖縄県,与那国島,30.3,35.0 94062,沖縄県,西表島,30.9,35.7 94081,沖縄県,石垣島,31.0,35.4 94086,沖縄県,盛山,30.9,34.7 94101,沖縄県,大原,30.4,35.7 94116,沖縄県,波照間,31.5,35.7 94121,沖縄県,志多阿原,31.6,35.0
以下の5つのカラムを持つTBLに、CSV ファイルからデータをロードします。
- 観測所番号
- 都道府県
- 地点
- 今年の最高気温（℃)（昨日まで）
- 昨日までの観測史上1位の値（℃）
create table temp(
id int,
pref text,
area text,
temp_max_year float,
temp_max float
);
copy temp from 'temp.csv' delimiter ',';
929レコードがロードできました。
postgres=# select count(*) from temp; count ------- 929 (1 行) postgres=# select * from temp limit 3; id | pref | area | temp_max_year | temp_max -------+----------------+--------+---------------+---------- 11001 | 北海道宗谷地方 | 宗谷岬 | 21.1 | 31.9 11016 | 北海道宗谷地方 | 稚内 | 20.9 | 31.3 11046 | 北海道宗谷地方 | 礼文 | 17.7 | 28.4 (3 行)
やってみた
1. PL/pgSQL でストアドファンクションをつくって動かしてみる
PostgreSQL 版 PL/SQL PL/pgSQL で、ストアドファンクションを作成します。
パラメータで指定した都道府県の、過去最高気温が MAX のレコード（最高気温が同じレコードが複数ある場合は、今年の最高気温 MAX レコード）を取得して返します。
CREATE TYPE temp_type AS (id INT, pref TEXT, area TEXT, temp_max_year FLOAT, temp_max FLOAT);
CREATE OR REPLACE FUNCTION get_temp(IN pref TEXT)
RETURNS SETOF temp_type AS $$
DECLARE ret RECORD;
BEGIN
SELECT t1.* INTO ret
FROM temp t1
WHERE t1.pref = $1 AND t1.temp_max = (
SELECT MAX(t2.temp_max)
FROM temp t2
WHERE t2.pref = $1
)
ORDER BY temp_max_year DESC
LIMIT 1;
RETURN NEXT ret;
RETURN;
END;
$$ LANGUAGE plpgsql;
DB に登録したら、'沖縄県' を指定して実行してみます。
postgres=# select get_temp('沖縄県');
get_temp
---------------------------------
(94001,沖縄県,伊原間,30.9,36.1)
(1 行)
都道府県を '埼玉県' に変更してみます。
postgres=# select get_temp('埼玉県');
get_temp
-----------------------------
(43056,埼玉県,熊谷,34,40.9)
(1 行)
ちゃんと指定した県の最高気温のレコードが返ってきました。
2. PL/Python で UDF を作って動かしてみる
PL/Python を PostgreSQL にインストール
「PL/Python ってなんぞや？」状態でした（あせ
処理の中身を Python で記述できるらしい。
他にも、Tcl や Perl 版もあるらしい。
まずは、PL/Python をインストールします。
DB につないで、以下のコマンドでインストールできるとのこと。
CREATE EXTENSION plpythonu;
実行してみたら・・・
postgres=# CREATE EXTENSION plpythonu; ERROR: ファイル"$libdir/plpython2"にアクセスできませんでした: No such file or directory
おこらりた。。
PostgreSQL インストールしたフォルダの lib ディレクトリの中見てみたら、確かに「plpython2 なひと、いませんよ」でした。。（代わりに、plpython3.dll がいました。
どうやら、plpython3u を指定すべし、らしい。
気を取り直して実行してみたら・・
postgres=# CREATE LANGUAGE plpython3u; ERROR: ライブラリ"C:/Program Files/PostgreSQL/9.6/lib/plpython3.dll"をロードできませんでした: The specified module could not be found.
またおこられた。。。（今度はちゃんと DLL あるのになー。。。
調べてみると、Python3.3 をご所望のようで。。（3系ならなんでもいいのかと思いきや。。。
Python のダウンロードページから、3.3 をダウンロードして PC にインストール後、環境変数にパスを追加して再度実行。
postgres=# CREATE LANGUAGE plpython3u; CREATE LANGUAGE postgres=#
PL/Python が使えるようになりました。
PL/Python で UDF を作って動かしてみる
同じく、指定都道府県の最高気温が MAX のレコードを返すユーザー定義関数( UDF )を、PL/Python で作りました。
パラメータ指定の都道府県のレコードを select した後、気温 MAX のレコード抽出は Python で処理します。
CREATE OR REPLACE FUNCTION get_temp_plpy(IN pref TEXT)
RETURNS SETOF TEXT
AS $$
plan = plpy.prepare("SELECT * FROM temp WHERE pref = $1", [ "text" ])
rv = plpy.execute(plan, [ pref ])
list_max = [rec for rec in rv if rec['temp_max'] == max([rec['temp_max'] for rec in rv])]
list_max = sorted(list_max, key=lambda x:x['temp_max_year'], reverse=True)
return list_max[0:1]
$$ LANGUAGE plpython3u;
実行してみます。
postgres=# select get_temp_plpy('沖縄県');
get_temp_plpy
--------------------------------------------------------------------------------------------
{'id': 94001, 'area': '伊原間', 'temp_max': 36.1, 'temp_max_year': 30.9, 'pref': '沖縄県'}
(1 行)
ストアドファンクションの実行結果と同様の、最高気温のレコードが取れました。
PL/Python だと何がうれしいの？
今回のお試し UDF では「何がうれしいの？（SQL で SELECT できるのに。。」状態ですが。。
PL/PgSQL がかけなくても Python でDBサイドで動くモジュールを作れるのはうれしい！（ふつうの PL って、配列の扱い方とかクセがあって、慣れるまで大変だと思うのです。。
また、今回作ってみた UDF はテーブルから取得したレコードを返すものでしたが、一般的には入力値に対する計算結果など（スカラ値）を返す UDF が多いとのことで。
psndas や numpy などの Python ライブラリが使えると、データ処理が楽になりますねｖ
おわりに（所感、わかったこと）
- RDBMS ごとに似て非なる PL 。。（共通化してくれればいいのになぁ。。。
- PL/pgSQL で関数作るときは最後に LANGUAGE 宣言が入るらしい。
- PL/Python なら普通の Python コード書く感覚で実装できるのでらくちん。
- PL/pgSQL より PL/Python（とか、PL/Perl とか）の方が、可読性もメンテナンス性もいいと思う。
