iakioの日記 RSSフィード

2010-08-10

PostgreSQLのマニュアルでよく使われている英単語(をPostgreSQLで調べる)その2

02:43 |  PostgreSQLのマニュアルでよく使われている英単語(をPostgreSQLで調べる)その2 - iakioの日記 を含むブックマーク はてなブックマーク -  PostgreSQLのマニュアルでよく使われている英単語(をPostgreSQLで調べる)その2 - iakioの日記

そういえば最近のPostgreSQLにはファイルシステムにアクセスする関数もあるんじゃなかったっけ。あれを使えばプログラムを書かなくてもドキュメントをインポートできるんじゃないのか?とふと思ったので調べてみたら、pg_ls_dir()/pg_read_file()/pg_stat_file()という関数がありました。これはいけるかも!!

=# select * from pg_ls_dir('.');
    pg_ls_dir
-----------------
 PG_VERSION
 pg_twophase
 pg_clog
 postgresql.conf
 pg_hba.conf
 pg_notify
 base
 pg_multixact
 pg_xlog
 pg_tblspc
 postmaster.opts
 pg_subtrans
 global
 pg_ident.conf
 postmaster.pid
 pg_stat_tmp
(16 rows)

=# select * from pg_ls_dir('/home');
ERROR:  absolute path not allowed
=# select * from pg_ls_dir('..');
ERROR:  reference to parent directory ("..") not allowed

ですよねー。そりゃそうだ。でもまあこの3つの関数と再帰クエリーがあれば、ディレクトリを再帰的に辿ってファイルを読み込むことができるはず。

=# select pg_stat_file(pg_ls_dir) from pg_ls_dir('.');
                                     pg_stat_file
---------------------------------------------------------------------------------------
 (4,"2010-08-11 00:38:04+09","2010-08-06 14:30:23+09","2010-08-06 14:30:23+09",,f)
 (4096,"2010-08-11 00:38:04+09","2010-08-06 14:30:23+09","2010-08-06 14:30:23+09",,t)
 (4096,"2010-08-07 04:02:09+09","2010-08-06 14:30:23+09","2010-08-06 14:30:23+09",,t)
...
 (1636,"2010-08-11 00:38:04+09","2010-08-06 14:30:23+09","2010-08-06 14:30:23+09",,f)
 (44,"2010-08-11 00:38:04+09","2010-08-11 00:38:04+09","2010-08-11 00:38:04+09",,f)
 (4096,"2010-08-10 04:02:08+09","2010-08-11 01:09:36+09","2010-08-11 01:09:36+09",,t)
(16 rows)

うーん欲しいのはこうじゃないんだよな。pg_stat_file()をfrom句に書いた時のように、

=# select * from pg_stat_file('.');
 size |         access         |      modification      |         change         | creation | isdir
------+------------------------+------------------------+------------------------+----------+-------
 4096 | 2010-08-11 01:14:49+09 | 2010-08-11 01:14:33+09 | 2010-08-11 01:14:33+09 |          | t
(1 row)

こういうのが欲しい。

=# select (pg_stat_file(pg_ls_dir)).* from pg_ls_dir('.');
 size  |         access         |      modification      |         change         | creation | isdir
-------+------------------------+------------------------+------------------------+----------+-------
     4 | 2010-08-11 00:38:04+09 | 2010-08-06 14:30:23+09 | 2010-08-06 14:30:23+09 |          | f
  4096 | 2010-08-11 00:38:04+09 | 2010-08-06 14:30:23+09 | 2010-08-06 14:30:23+09 |          | t
  4096 | 2010-08-07 04:02:09+09 | 2010-08-06 14:30:23+09 | 2010-08-06 14:30:23+09 |          | t
...
  1636 | 2010-08-11 00:38:04+09 | 2010-08-06 14:30:23+09 | 2010-08-06 14:30:23+09 |          | f
    44 | 2010-08-11 00:38:04+09 | 2010-08-11 00:38:04+09 | 2010-08-11 00:38:04+09 |          | f
  4096 | 2010-08-10 04:02:08+09 | 2010-08-11 01:11:06+09 | 2010-08-11 01:11:06+09 |          | t
(16 rows)

これこれ。なるほど、"(pg_stat_file(pg_ls_dir)).*"でいいのか。じゃあインチキだけどドキュメントを$PGDATAにシンボリックリンク。

$ cd data
$ ln -s path/to/src/postgresql-9.0beta4/doc/src/sgml/html

とここで誤算。PostgreSQLHTMLドキュメントはフラット構造なので再帰クエリー必要無いことに気づきました、、、まあいずれやってみるとして。

=# insert into pg90doc
-# select pg_ls_dir, contents, to_tsvector('english', contents)
-#  from (select pg_ls_dir, pg_read_file('html/' || pg_ls_dir, 0, size) as contents
-#          from (select (pg_stat_file('html/' || pg_ls_dir)).*, pg_ls_dir
-#                  from pg_ls_dir('html')
-#               ) as s1
-#       ) as s2;

なんか酷いけどできました。WITH使った方がいいのかな。

=# insert into pg90doc
-# with
-#  s1 as (select (pg_stat_file('html/' || pg_ls_dir)).*, pg_ls_dir from pg_ls_dir('html')),
-#  s2 as (select pg_ls_dir, pg_read_file('html/' || pg_ls_dir, 0, size) as contents from s1)
-# select pg_ls_dir, contents, to_tsvector('english', contents) from s2;
INSERT 0 1025

おーこの方が全然いいですね。サブクエリの内側に書いてたものを前側に書けた感じで、処理の順番通りになりました。やっぱWITH句強力だわ。モダンPostgreSQL使いの必修スキルですね。

最後に、8.4のドキュメントと9.0のドキュメントの単語の出現数の比較をやってみます。

=# with
 s84 as (select word, nentry from ts_stat('select vec from pg84doc')),
 s90 as (select word, nentry from ts_stat('select vec from pg90doc'))
select word, s90.nentry, s84.nentry, s90.nentry - s84.nentry,
  rank() over (order by s90.nentry - s84.nentry desc)
  from s84 join s90 using (word) limit 30;
    word    | nentry | nentry | ?column? | rank
------------+--------+--------+----------+------
 9.0        |   1077 |      3 |     1074 |    1
 name       |   4907 |   4458 |      449 |    2
 use        |   6218 |   5771 |      447 |    3
 standbi    |    464 |    132 |      332 |    4
 server     |   2467 |   2159 |      308 |    5
 function   |   5489 |   5207 |      282 |    6
 set        |   3083 |   2803 |      280 |    7
 pg         |   3104 |   2851 |      253 |    8
 valu       |   3827 |   3581 |      246 |    9
 paramet    |   2120 |   1888 |      232 |   10
 tabl       |   5089 |   4867 |      222 |   11
 file       |   2210 |   2028 |      182 |   12
 type       |   4608 |   4430 |      178 |   13
 wal        |    568 |    401 |      167 |   14
 default    |   1843 |   1678 |      165 |   15
 column     |   2750 |   2586 |      164 |   16
 row        |   2981 |   2819 |      162 |   17
 new        |   1860 |   1701 |      159 |   18
 allow      |   1563 |   1410 |      153 |   19
 specifi    |   1833 |   1686 |      147 |   20
 postgresql |   3133 |   2992 |      141 |   21
 return     |   2802 |   2661 |      141 |   21
 queri      |   2384 |   2244 |      140 |   23
 databas    |   2981 |   2843 |      138 |   24
 option     |   1752 |   1619 |      133 |   25
 string     |   1736 |   1603 |      133 |   25
 primari    |    396 |    266 |      130 |   27
 45         |    145 |     19 |      126 |   28
 trigger    |   1108 |    983 |      125 |   29
 user       |   2060 |   1936 |      124 |   30
(30 rows)
トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20100810