PostgreSQL 標準入力から複数列を受け取って配列に落とし込む

# まえがき
シェルから複数列の標準入力をハンドリングする際のメモ。その場でさくっと実行したいときがあった。

# 参考文献

https://www.postgresql.jp/document/9.3/html/functions-array.html
https://qiita.com/choplin/items/9d5e2ff8721fb9509bf8
https://qiita.com/A-Kira/items/dde43e0e0aa7cec51938


# 環境

```bash
$psql --version
psql (PostgreSQL) 12.0
$bash --version
GNU bash, バージョン 5.0.0(1)-release (x86_64-pc-linux-gnu)
Copyright (C) 2019 Free Software Foundation, Inc.
ライセンス GPLv3+: GNU GPL バージョン 3 またはそれ以降 <http://gnu.org/licenses/gpl.html>

This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
```

# スクリプト

標準入力から渡す文字列。

```bash
$paste <(seq 1 10) <(printf "%s\n" $(echo -e '\U1f4a'{0..9}))
1	💠
2	💡
3	💢
4	💣
5	💤
6	💥
7	💦
8	💧
9	💨
10	💩
```

これをpostgresで受け取って改行区切り処理する。
pasteコマンドはデフォがタブ区切りなので、要素展開する際は**\t**を指定。

```bash
psql -q -v tgt="'$(paste <(seq 1 10) <(printf "%s\n" $(echo -e '\U1f4a'{0..9})))'" \
  -U postgres < <(cat<<EOS
  with sub as(
    select regexp_split_to_table(:tgt,'\n') as map
    ,regexp_split_to_array(:tgt,'\n') as ary
    ,generate_subscripts(regexp_split_to_array(:tgt,'\n'),1) as ind
  )
  select map
    ,ary
    ,ind
    ,ary[ind]as ele
    ,regexp_split_to_array(ary[ind],'\t')as done
  from sub;
EOS
);
```

マルチバイトだと表示幅うまく調節できないのかな。

```bash
    map    │                                         ary                                         │ ind │    ele    │  done  
───────────┼─────────────────────────────────────────────────────────────────────────────────────┼─────┼───────────┼────────
 1       💠 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │   1 │ 1       💠 │ {1,💠}
 2       💡 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │   2 │ 2       💡 │ {2,💡}
 3       💢 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │   3 │ 3       💢 │ {3,💢}
 4       💣 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │   4 │ 4       💣 │ {4,💣}
 5       💤 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │   5 │ 5       💤 │ {5,💤}
 6       💥 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │   6 │ 6       💥 │ {6,💥}
 7       💦 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │   7 │ 7       💦 │ {7,💦}
 8       💧 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │   8 │ 8       💧 │ {8,💧}
 9       💨 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │   9 │ 9       💨 │ {9,💨}
 10      💩 │ {"1     💠","2   💡","3   💢","4   💣","5   💤","6   💥","7   💦","8   💧","9   💨","10  💩"} │  10 │ 10      💩 │ {10,💩}
(10 rows)
```

単一行の場合は、**string_to_array**で行ける。

```bash
psql -q -v tgt="'$(echo -e 'U1f4a'{0..9})'" -U postgres  < <(cat<<EOS
  with sub as(
    select
      ind
      ,string_to_array(:tgt,' ') as ary
    from generate_subscripts(string_to_array(:tgt,' '),1) ind
  )
  select ind,ary,ary[ind]as ele from sub;
EOS
);
```

```bash
 ind │                                   ary                                   │  ele   
─────┼─────────────────────────────────────────────────────────────────────────┼────────
   1 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a0
   2 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a1
   3 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a2
   4 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a3
   5 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a4
   6 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a5
   7 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a6
   8 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a7
   9 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a8
  10 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a9
(10 rows)
```

単一行の場合は、**regexp_split_to_array**でいける。

```bash
psql -q -v tgt="'$(echo -e 'U1f4a'{0..9})'" -U postgres  < <(cat<<EOS
  with sub as(
    select
      ind
      ,regexp_split_to_array(:tgt,' ') as ary
    from generate_subscripts(string_to_array(:tgt,' '),1) ind
  )
  select ind,ary,ary[ind]as ele from sub;
EOS
);
```

```bash
 ind │                                   ary                                   │  ele   
─────┼─────────────────────────────────────────────────────────────────────────┼────────
   1 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a0
   2 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a1
   3 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a2
   4 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a3
   5 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a4
   6 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a5
   7 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a6
   8 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a7
   9 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a8
  10 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a9
(10 rows)
```

単一列の場合は、**regexp_split_to_array**で行ける。

```bash
psql -q -v tgt="'$(printf "%s\n" 'U1f4a'{0..9})'" -U postgres  < <(cat<<EOS
  with sub as(
    select
      ind
      ,regexp_split_to_array(:tgt,'\n') as ary
    from generate_subscripts(regexp_split_to_array(:tgt,'\n'),1) ind
  )
  select ind,ary,ary[ind]as ele from sub;
EOS
);
```

```bash
 ind │                                   ary                                   │  ele   
─────┼─────────────────────────────────────────────────────────────────────────┼────────
   1 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a0
   2 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a1
   3 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a2
   4 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a3
   5 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a4
   6 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a5
   7 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a6
   8 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a7
   9 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a8
  10 │ {U1f4a0,U1f4a1,U1f4a2,U1f4a3,U1f4a4,U1f4a5,U1f4a6,U1f4a7,U1f4a8,U1f4a9} │ U1f4a9
(10 rows)
```

# あとがき

マルチバイトでも表示幅うまく調節できないのかなー。

以上、ありがとうございました。