PL/pgSQL: テーブルを返す関数

はじめに

OSM(Open Street Map)をカスタマイズして使っている。 描画規則を簡単化するために、道路種別ごとに使用する線の太さ、色などが必要である。

元のテーブル planet_osm_line にこれらのカラムを追加すればクエリはシンプルになるが、 テーブルサイズが大きくなる。

テーブル planet_osm_line と定義テーブルを left join するのが効率的である。

下のようにダイレクトに クエリに 定義テーブルを組み込むことができる。

select ... from planet_osm_line l left join (values 
( 10, 1.0, '#dc2a67', '#e892a2', '#f0bcc5'),
( 15, 0.6, '#dc2a67', '#e892a2', '#f0bcc5'),
  [中略]
(196, 1.0,  null,     '#70b971',  null    )
) as v (way_id, ... ) on v.way_id = l.way_id  where ... 

しかし、この記述が複数個所に現れると、全体の行数はかさばる。

このテーブルを PL/pgSQLの関数で返すことができれば、 多分、テーブル v をデータベース上に置いて left join するよりも効率がいいだろう。

この (values 〜 ) as v (way_id, ... ) を関数の戻り値としたい。

テーブルを返す関数

以下に示すように、 returns table( ... ) でテーブルを定義し、return query select ... で各行を返せばよい。

create or replace function my_highways()
    returns table(
        way_id int, sw_factor real, case_stroke text, fill_stroke text, fill_tunnel_stroke text
    ) as $$
begin
    return  query select 10, 1.0::real, '#dc2a67', '#e892a2', '#f0bcc5';
    return  query select 15, 0.6::real, '#dc2a67', '#e892a2', '#f0bcc5';
    return  query select 20, 1.0::real, '#c84e2f', '#f9b29c', '#fbd6cb';
    return  query select 25, 0.6::real, '#c84e2f', '#f9b29c', '#fbd6cb';
    return  query select 30, 1.0::real, '#a06b00', '#fcd6a4', '#fdecd4';
    return  query select 35, 0.6::real, '#a06b00', '#fcd6a4', '#fdecd4';
    return  query select 40, 1.0::real, '#707d05', '#f7fabf', '#f9fcd6';
    return  query select 45, 0.6::real, '#707d05', '#f7fabf', '#f9fcd6';
    return  query select 50, 1.0::real, '#999',    '#fff',    '#fff'   ;
    return  query select 55, 0.6::real, '#999',    '#fff',    '#fff'   ;
    return  query select 90, 0.6::real, '#ff5675', '#ffc0cb', '#ffe5ea';
    return  query select 110, 1.0::real, '#999',    '#fff',    '#fff'   ;
    return  query select 120, 1.0::real, '#999',    '#fff',    '#fff'   ;
    return  query select 130, 1.0::real, '#999',    '#fff',    '#fff'   ;
    return  query select 150, 0.4::real, '#999',    '#ddd',    '#ddd'   ;
    return  query select 160, 0.5::real, '#999',    '#fff',    '#fff'   ;
    return  query select 165, 0.3::real, '#999',    '#fff',    '#fff'   ;
    return  query select 170, 0.6::real, '#999',    '#dddde8', '#dddde8';
    return  query select 175, 0.3::real, '#999',    '#dddde8', '#dddde8';
    return  query select 181, 0.5::real, '#b09b4f', '#ede8d8', '#ede8d8';
    return  query select 182, 0.4::real, '#b09b4f', '#cfc295', '#cfc295';
    return  query select 189, 0.3::real, '#b09b4f', '#bfae72', '#bfae72';
    return  query select 190, 0.8::real,  null,     '#999',     null    ;
    return  query select 192, 0.6::real,  null,     '#999',     null    ;
    return  query select 194, 1.0::real,  null,     '#7876f5',  null    ;
    return  query select 196, 1.0::real,  null,     '#70b971',  null    ;
end;
$$ LANGUAGE plpgsql;

参照はテーブル名のところを関数名 my_highways() とすればよい。

select ... from planet_osm_line l 
left join my_highways() v on v.way_id = l.way_id where ... 

おわりに

マイOSM地図のレンダリングでは my_highways() 関数は 22回呼び出される。 その分 Mapnikスタイルファイル(Mapnikによるレンダリングで使用される)はスリムになった。 しかし、関数コールのオーバヘッドがあるだろうから、 実行時間は微増しているかも知れない。 微々たる値のため、実測しても増えたか減ったか分からない。

参考文献