新年なのでバナー表示SQL関数を作ってみた

はじめに

みなさん、あけましておめでとうございます。
ぬこ＠横浜です。今年も早速、PostgreSQLを使ってくだらないことをしてみます。

新年なので、バナー表示用のSQL関数を作ってみた。

そう、今は新年。やっぱりA HAPPY NEW YEARしたいじゃないですか。
どうせするなら、でっかく表示したい。そう、bannerコマンドのように。
以前、UNIX/Linuxには、bannerという英文字をバナー表示するようなツールがあるんだけど(*1)、なぜか(?)PostgreSQLのSQL関数にはバナー表示するSQL関数がない！(あるわけねーだろ)。

ということでないので作ってみた。

(*1) なんか最近のCentOSにはbannerコマンドが入ってないので、別途DL＆ビルドしないといけないっぽい。

実行例

banner()という集合を返す関数を作成する。
この関数に英字のテキストを与えると、その英字の(大文字の)バナーのテキスト集合を返却する。

新年らしく、A HAPPY NEW YEARしてみましょ。

[nuko@localhost banner]$ psql test -e -f test.sql
SELECT * FROM banner('a happy');
                        data                         
-----------------------------------------------------
    #       #     #    #    ######  ######  #     # 
   # #      #     #   # #   #     # #     #  #   #  
  #   #     #     #  #   #  #     # #     #   # #   
 #     #    ####### #     # ######  ######     #    
 #######    #     # ####### #       #          #    
 #     #    #     # #     # #       #          #    
 #     #    #     # #     # #       #          #    
(7 rows)

SELECT * FROM banner('new year');
                            data                             
-------------------------------------------------------------
 #     # ####### #     #    #     # #######    #    ######  
 ##    # #       #  #  #     #   #  #         # #   #     # 
 # #   # #       #  #  #      # #   #        #   #  #     # 
 #  #  # #####   #  #  #       #    #####   #     # ######  
 #   # # #       #  #  #       #    #       ####### #   #   
 #    ## #       #  #  #       #    #       #     # #    #, 
 #     # #######  ## ##        #    ####### #     # #     # 
(7 rows)

[nuko@localhost banner]$ 

実装

最初はSQLとplpgsqlだけを使って簡単にできるかと思ったけど、意外と面倒だったなあ。
9.4から追加された ROWS FROM 構文を使ったり、plpgsqlのRETURN NEXT を使ったりと、なかなか楽しめた。

ポイント1 UNIONとORDER BY

最初、各英文字をバナー化するFUNCTIONを以下のように作成したのだが、

CREATE OR REPLACE FUNCTION banner_a()
 RETURNS SETOF text
 LANGUAGE sql
 -- common options:  IMMUTABLE  STABLE  STRICT  SECURITY DEFINER
AS $function$
SELECT tx FROM (
SELECT 1 as id , '   #    ' as tx  UNION
SELECT 2 as id , '  # #   ' as tx  UNION
SELECT 3 as id , ' #   #  ' as tx  UNION
SELECT 4 as id , '#     # ' as tx  UNION
SELECT 5 as id , '####### ' as tx  UNION
SELECT 6 as id , '#     # ' as tx  UNION
SELECT 7 as id , '#     # ' as tx
) AS t ORDER BY id
$function$
;

まあ、こんな感じの関数をbanner_aからbanner_zまで作っておく。

ポイント2 ROWS FROM

で、この関数出力結果を横に並べるにはどうするか、というところで悩んだ。
これを解決するために、PostgreSQL 9.4から追加されたSELECT文のROWS FROM構文を利用した。
例えばこんな感じ。

test=# SELECT banner_a || banner_e FROM ( SELECT * FROM ROWS FROM ( banner_a(), banner_e())) as _x ;
     ?column?     
------------------
    #    ####### 
   # #   #       
  #   #  #       
 #     # #####   
 ####### #       
 #     # #       
 #     # ####### 
(7 rows)

さて、これを見ると分かるように、ROWS FROM () には、各バナー文字用の集合返却関数を複数書いているのだが、bannerを実装するときには、ここに全てのbanner_* 関数を書いておけばいい。
で、動的にSELECT句の文字列連結箇所を、banner関数に与えた引数によって生成すれば良い。
生成したものを専用のTEMP TABLEにINSERTして、そのTEMP TABLEをあとは検索すればOK。

コードはもうちょっと整備してから、Githubに上げる予定です。

おわりに

ということで、年始から役に立たないPostgreSQLの使い方を書きました。
今年もこの調子で、ガンガン役に立たないPostgreSQLネタを考えてみようと思います。