象と戯れ

 | 

2010-03-10

SETOF関数のあれこれ

17:38 | SETOF関数のあれこれ - 象と戯れ を含むブックマーク はてなブックマーク - SETOF関数のあれこれ - 象と戯れ

generate_seriesについてよくまとまってます。

SQLで,テーブルに存在しない複数行のデータを取得する方法 (PostgreSQLの generate_series関数をマスターしよう) - 主に言語とシステム開発に関して

PostgreSQLで,テーブルを使わずに,複数行のデータを取得する方法。

* SQLの中で,大量の定数データを使う必要がある。(例えば,1ヶ月間の日付全部とか,1000までの素数全部とか)

* だが,それらのデータを,前もってどこかのテーブルに格納しておくことはできない。(データが多すぎるから・・・)


そんなとき,「複数行の使い捨てデータを動的に生成する」というテクニックが役立つ。

テーブルやシーケンスは使わない。

...

generate_seriesという関数を使えば,複数行のデータを返すことが可能。

この関数は,連続した数値(連番)を複数行で返す。

そのような関数を、SETOF関数と呼びます。generate_seriesを始めとするSETOF関数はトリッキーながら様々な使い方ができて個人的には大好きです。引用元にあるように素数やカレンダーを作ったりするだけでなく、接尾辞のリストを作ったりあえて行を増やして集約の幅を広げたりと用途は様々です。

軽くググっただけでも

出現文字を数え上げる - 象と戯れ - postgresqlグループ

配列を行に分解 - iakioの日記 - postgresqlグループ

マンデルブロ集合 by WITH query - 象と戯れ - postgresqlグループ

13日の金曜日を数え上げる - PostgreSQL編 - 象と戯れ - postgresqlグループのコメント欄

などなどpostgresqlグループでも結構頻出ですね。あとSQLで数独を解く - y-kawazの日記とか。

ところでgenerate_seriesに関して、

※注:

PostgreSQLには「配列型」というれっきとしたデータ型が存在する。

なので,ここで試した複数行のレコードを,真の意味で「配列」と呼ぶことはできない。

という風に書かれているですが、実はSQLの行と配列は「リスト」という意味で相互補完関係にあるといってもいい。というのも、PostgreSQLの配列はデータとして保存し一要素に対し読み出しや書き込みをするのは悪くないのだが、全ての要素に対する処理(つまりはループ処理)がSQL上で扱いづらい。そこで関数を作って

CREATE OR REPLACE FUNCTION add_each(ary int4[], val int4) RETURNS int4[] AS $$
SELECT ARRAY(SELECT $1[i] + $2 FROM generate_series(1, array_upper($1, 1)) i)
$$ LANGUAGE sql IMMUTABLE;

のようにやると、配列の各要素にスカラー値を足す処理が記述できる。

SELECT add_each(array[1,10,100], 2);
  add_each  
------------
 {3,12,102}
(1 row)

もちろんPL/pgSQLを使えば同じことができるわけですが、この程度であればSQL関数の方が気楽だし8.4あたりからSQLにインライン化されたりされなかったりしてお得なわけです。

ちなみにgenerate_seriesはFROM句だけではなくSELECT句の中でも使えます。

SELECT generate_series(1, 10) AS i, generate_series(1, 3) AS j

複数のカラムでSETOF関数を呼ぶと、それぞれの出力行数の最小公倍数の行数が出力されます。上記の例だと(1, 1)から始まって(10, 3)まで30行ですね。

8.4で調べる限りこのSETOF関数は以下のようなものがあります。

  • generate_subscripts:配列の添え字を列挙する
  • unnest:配列の要素を列挙する
  • regexp_matches:文字列を正規表現でマッチしたところで行に分割、キャプチャで各行に配列を作る(知らなかった・・・)
  • regexp_split_to_table:正規表現でsplitして行にする
  • pg_get_keywords:SQLキーワードの一覧を返す
  • pg_options_to_table:"key=value"の形式の文字列配列を受け取って、key列とvalue列の行で返す
  • pg_stat_get_backend_idset
  • pg_stat_get_activity
  • pg_show_all_settings:SETコマンドの設定を行で返す
  • pg_lock_status
  • pg_prepared_xact
  • pg_ls_dir
  • pg_prepared_statement
  • pg_cursor
  • pg_timezone_abbrevs
  • pg_timezone_names
  • generate_series:言わずとしれた連番生成
  • pg_tablespace_databases

pg_で始まる連中は裏コマンドとしてpg_dumpやpgAdmin等が使っているようですね。全部説明できる人がいたら解説よろしく!

generate_seriesでできないこと

SQLを使って,検索結果に連番をふりたい」というニーズがよくあるが,それはgenerate_seriesではできない。

>=8.4ならそれrow_number()でおk

あと、>=8.4なら generate_series(timestamp, timestamp, interval) も(ry

AketiJyuuzouAketiJyuuzou2010/04/29 18:31マニュアルの「34.4.7. 集合を返すSQL関数」
にselect句でのgenerate_seriesなどの集合を返す関数の使用は、
非推奨で、今後のリリースでは削除される可能性があります。と記述されていますが

この記述がなくなるとうれしいです。

umitanukiumitanuki2010/04/30 06:22この記述は将来LATERALをサポートすることを意識しているためにあるのですが、これまでのPostgreSQLのやり方から言ってLATERALサポート後にすぐにgenerate_seriesがサポートされなくなるということはないと思います。

 |