第2回：PostgreSQL-カンマ区切りデータの変換（regexp_split_to_table）

技術者向け・データベースの技術情報発信

2017.03.30

はじめに

こんにちは、マルチDBソリューション部の川本です。今回の技術情報は、AＷS上で対応した案件で苦労した部分についての対応内容のまとめです。

要件

EC2上に配置したExcelをDB（PostgreSQL）のテーブルにインポートする
インポートしたテーブルには、1つの列にカンマ区切りの数値データ（1,1など）が格納されており、カンマで区切られている数値データをそれぞれ足し算し、加算後の数値にアップデートする
カラムに格納されているデータをそのまま足し算するのではなく、各数字をマスター表と突き合わせ、変換後の数値どうしを足し算する
上記内容をすべて自動化
作業に使用できるサーバはLinux

環境

やりたいこと

課題

Linux上で、ExcelをどのようにDBにインポートするのか
→こちらは、「xlsx2csv.py」で解決しました。
カンマ区切りのデータ（1,1など）と、マスター表のデータをそのまま結合できない
→解決方法として、カンマ区切りのデータを、regexp_split_to_table関数を使用し、以下のようにテーブルを分割してから、マスター表と結合しました。
（【参考】PostgreSQLマニュアル:9.4. 文字列関数と演算子）

例：w_tbl1テーブルの分解（c1 = ‘A’）

参考ですが、案件では以下のようなファンクションを作成し対応しました。

CREATE OR REPLACE FUNCTION public.fnc_calc_price3（
    p_table_name character varying）
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE NOT LEAKPROOF 
AS $function$

DECLARE
  v_rtn_msg     VARCHAR（100）;
  v_row_name    VARCHAR（1000）;
  v_tbl_name    VARCHAR（20）;
  v_mst_name    VARCHAR（20）;
  v_wk1_name    VARCHAR（20）;
  v_wk2_name    VARCHAR（20）;

  cur_csv_data  REFCURSOR;
  cur_price     REFCURSOR;
  cur_row_name  REFCURSOR;

  rec_csv_data  RECORD;
  rec_price     RECORD;
  rec_row_name  RECORD;

  i_total_price   INTEGER;
  i_price       INTEGER;
  i_reg_cnt     INTEGER;
BEGIN
  -- テーブル名設定
  v_tbl_name := 't_'||p_table_name;
  v_mst_name := 'm_'||p_table_name;
  v_wk1_name := 'w_'||p_table_name||'1';
  v_wk2_name := 'tmp_'||p_table_name||'2';

  -- 計算用ワークテーブル作成
  EXECUTE 'CREATE TEMPORARY TABLE '
               ||v_wk2_name
               ||' ON COMMIT DROP as SELECT c1,c2 FROM '
               ||v_wk1_name::regclass;

  -- 計算用ワークテーブルへＰＫ追加
  EXECUTE 'ALTER TABLE '
               ||v_wk2_name::regclass
               ||' ADD CONSTRAINT pk_'
               ||v_wk2_name::regclass||' PRIMARY KEY（c1）';
  
  OPEN cur_csv_data FOR EXECUTE 'SELECT c1 FROM ' || v_wk2_name::regclass;
  LOOP
    FETCH cur_csv_data INTO rec_csv_data;
    IF NOT FOUND THEN
      EXIT;
    END IF;

    -- 金額設定処理
    i_total_price := 0;
    OPEN cur_price FOR EXECUTE 'SELECT regexp_split_to_table（c2, '',''） as price_cd, COUNT（*） as cnt'
                                     || ' FROM ' || v_wk2_name::regclass
                                     || ' WHERE c1 = $1 '
                                     || ' GROUP BY regexp_split_to_table（c2, '',''）'
                                     || ' ORDER BY regexp_split_to_table（c2, '',''）'
                             USING rec_csv_data.c1;
    LOOP
      FETCH cur_price INTO rec_price;
      IF NOT FOUND THEN
        EXIT;
      END IF;

      -- 金額取得
      i_price := 0;
      EXECUTE 'SELECT price FROM ' || v_mst_name::regclass
                                   || ' WHERE code = $1'
        INTO i_price
        USING rec_price.price_cd::integer;

      -- 合計金額計算
      i_total_price := i_total_price + （COALESCE（i_price,0） * rec_price.cnt）;

    END LOOP;
    CLOSE cur_price;

    -- 金額設定
    EXECUTE 'UPDATE ' || v_wk2_name::regclass
                      || ' SET c2 = $1'
                      || ' WHERE c1      = $2'
      USING i_total_price, rec_csv_data.c1;

  END LOOP;
  CLOSE cur_csv_data;

  -- テーブルデータ入替え
  EXECUTE 'TRUNCATE TABLE ' || v_tbl_name::regclass;
  EXECUTE 'INSERT INTO ' || v_tbl_name::regclass
                         || ' SELECT c1,c2::integer'
                         || ' FROM '
                         || v_wk2_name::regclass || ' wk2'
                         || ' ORDER BY c1';

  -- 登録データ件数カウント
  EXECUTE 'SELECT COUNT（*） FROM ' || v_tbl_name::regclass
     INTO i_reg_cnt;


  -- データ件数リターン
  RETURN v_tbl_name || ' : ' || i_reg_cnt;

EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    SELECT 'Table name specification error' INTO v_rtn_msg;
    RETURN v_rtn_msg;
  WHEN OTHERS THEN
    v_rtn_msg := SQLSTATE || ':' || SQLERRM;
    RETURN v_rtn_msg;
END;

$function$;

データベース運用保守サービス