象と戯れ

 | 

2011-05-13

writable CTE in 9.1

23:10 | writable CTE in 9.1 - 象と戯れ を含むブックマーク はてなブックマーク - writable CTE in 9.1 - 象と戯れ

betaとなって機能もFixした9.1ですが、いつものようにLet'sにはすばらしい新機能紹介ページが用意されています。

PostgreSQL 9.1 の新機能 | Let's Postgres

本日時点で下記のように紹介されているwritable CTEですが、

WITH 句を使った INSERT, UPDATE, DELETE

WITH (...) INSERT/UPDATE/DELETE ができるようになりました。WITH RECURSIVE も使えるので、再帰クエリの結果を元にテーブルを更新するようなケースで便利かもしれません。

というのは最後のCommitFestの途中までで、最終的にはWITH()の中にもINSERT/UPDATE/DELETEが書けるようになりました。Marko++

この機能自体は(似たようなものがSQL Serverとかにはあるものの)、標準でもまだ規定されていないレベルで、まさに前衛的データベースを体現しております。

何がうれしいのか、その使い方はユーザそれぞれですが、一つの参考例として、MySQLのUPSERTを模倣した例が挙がっています。

Upserting via Writeable CTE - zillablog

簡単にすると、こんな感じ。

TABLE target;
 id | val 
----+-----
  1 | Tom
(1 row)

TABLE source;
 id | val 
----+-----
  1 | Mom
  2 | Bob
(2 rows)

WITH upsert AS (UPDATE target SET val = source.val FROM source WHERE target.id = source.id RETURNING source.id)
INSERT INTO target
SELECT id, val FROM source WHERE id NOT IN (SELECT id FROM upsert);
INSERT 0 1

TABLE target;
 id | val 
----+-----
  1 | Mom
  2 | Bob
(2 rows)

targetテーブルをUPDATEして、FROM句で結合されなかったidだけINSERT ... SELECTするという。MERGEコマンドが入るまではこんな感じで悩みを解決してくれそう。

Enjoy!

twitter_fdw 1.0.0 released

22:56 | twitter_fdw 1.0.0 released - 象と戯れ を含むブックマーク はてなブックマーク - twitter_fdw 1.0.0 released - 象と戯れ

9.1がbetaに入り、FDWインターフェイスが固まったことで、先日実験的に作成したtwitter_fdwをリリースしました。

twitter_fdw: a foreign data wrapper to Twitter Search / PostgreSQL Extension Network

PGXNのページがカッコイイ!のでイカした感じがしますね。現在のところPublic Search APIのみを提供しますが、CREATE USER MAPPINGでOAuthを使ったユーザ系APIも叩けるようになるといいと思っています。それは2.0系としてのお楽しみ。

ついでにpgxnclientを試しました。

GitHub - dvarrazzo/pgxnclient: A command line client for the PostgreSQL Extension Network

$ sudo apt-get install python-setuptools
$ sudo easy_install pgxnclient
$ pgxn install --nosudo tinyint

最初の二つはUbuntuでeasy_install入れてpgxnclient導入するためのものですので、実質一行。make installにsudoを使わない場合は--nosudoオプションでOK。cpanmよろしくモジュール名をコマンドラインで叩くだけでダウンロード&インストール。PGXN開設から1ヶ月弱だと思いますが、このスピードですばらしいクライアントを実装してeasy_installサイトにまで登録してるDanielは結構イカしてると思う。

試してないですが、9.1をインストールしてあれば

$ pgxn install --nosudo twitter_fdw

でさくっと入るはずです。あとは

$ psql mydb
mydb=# CREATE EXTENSION twitter_fdw;
mydb=# SELECT from_user, text, created_at FROM twitter WHERE q = '@planetpostgres';

などとすればステキな感じです。

Enjoy!

 |