
ãITããåå¿é²ãBashã§psqlã³ãã³ããé ååããã `-F $'\t' -t` ãå¹ããªãïŒåå ãšè§£æ±º
çããŸãããã«ã¡ã¯
PostgreSQLã³ãã³ãæäœæã®åå¿é²ãèšèŒããŸãã
ã¯ããã«
Bashã§ `psql` ãåŒã³åºããšããæ¥ç¶æ å ±ïŒhost/user/db ãªã©ïŒãåºå圢åŒïŒåºåãæåããããç¡ããªã©ïŒãæŽçããããŠã
psqlæ¬äœã®ã³ãã³ããé å `PSQL=(...)` ã«ãã
åºåçšãªãã·ã§ã³ãé å `PSOP=(...)` ã«ãã
æåŸã« `"$PSQL" "$PSOP"` ãåäœããŠå®è¡ãã
ãšãã圢ãåãããšããããŸãã
ãã®æ¹æ³ã¯ãç°å¢å€æ°ã« `"psql -h ... -U ..."` ã®ãããªæååã³ãã³ããå ¥ããããå®å šã§ãããšãã远å ãããããã§ãã
ãã ãæ¬¡ã®ãããªæžãæ¹ããããšã`PSOP` ãå¹ããªãããã«èŠãããããšããããŸããã
PSOP=(-A -F $'\t' -t)
${PSQL[@]} -f filename.sql ${PSOP[@]}ãã®èšäºã§ã¯ããã®çç¶ã®åå ãšã確å®ã«çŽãæžãæ¹ãæŽçããŸããã
0. åæïŒä»åããããããšïŒãŽãŒã«ïŒ
ããšãã° SQL ã®åºåã
眫ç·ãæŽåœ¢è¡šç€ºããããŠïŒunalignedïŒ
ããããæ¶ããŠïŒtuples onlyïŒ
åºåããã¿ãïŒTSVïŒã«ããŠ
åŠçãããã圢ã§åãããããšããç®çã§ãã
psql ã®ãªãã·ã§ã³ã ãšã ãããããïŒ
`-A` : aligned衚瀺ããããïŒunalignedïŒ
`-t` : ããããè¡æ°è¡šç€ºãæ¶ãïŒtuples onlyïŒ
`-F $'\t'` : åºåãæåãã¿ãã«ããïŒField separatorïŒ
1. ãŸããæ£ããåå°ãïŒpsqlã³ãã³ãã¯é åã§æã€
Bashã§å®å šã«ãããªããpsqlã³ãã³ãã¯ããããé åã«ããŸãã
PSQL=(psql -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME")é åã«ããã¡ãªãã
ã¹ããŒã¹ãå«ãå€ã§ãå£ãã«ãã
`eval` äžèŠïŒäºæ ãæžãïŒ
åŒæ°ã®è¿œå ã»åé€ã楜
2. åºåãªãã·ã§ã³ãé åã§æã€
ä»åã®åºåæŽåœ¢ãªãã·ã§ã³ãé åã«ããŸãã
PSOP=(-A -F $'\t' -t)ãããŸã§ã§æºåã¯OKã
3. çç¶ïŒPSOPãä»ããã®ã«å¹ããªãïŒããã«èŠããïŒ
åé¡ã®æžãæ¹ããã¡ãã
PSOP=(-A -F $'\t' -t)
${PSQL[@]} -f filename.sql ${PSOP[@]}ãã®åœ¢ã ãšãç°å¢ã«ãã£ãŠã¯
`-F` ã®åºåããã¿ãã«ãªã£ãŠããªã
`-t` ã§ããããæ¶ããã¯ããªã®ã«æ¶ããªã
`-A` ã§ãæŽåœ¢è¡šç€ºã£ãœãèŠãã
ãªã©ããå¹ããŠãªããããã«èŠããããšããããŸãã
4. åå â ïŒæéèŠïŒïŒé åå±éãã¯ã©ãŒãããŠããªã
çµè«ããèšããšãåå ã®ç¬¬äžåè£ã¯ããã§ãã
`${PSOP[@]}` ãã¯ã©ãŒãç¡ãã§å±éããŠãã
Bashã§ã¯ãã¯ã©ãŒãç¡ãã®å±éã¯
空çœ
ã¿ã
æ¹è¡
ãªã©ã§**åå²ïŒword splittingïŒ**ãããŸãã
ãããŠä»åã® `-F $'\t'` ã¯ãåŒæ°ã âã¿ããã®ãã®â ã§ãã
ã€ãŸããã¯ã©ãŒãç¡ãã ãšã¿ããåå²ãããŠããŸããçµæãšã㊠`-F` ã®åŒæ°ãå£ããã空æ±ãã«ãªã£ããããŸãã
â 解決çïŒé åã¯å¿ ã `"${arr[@]}"` ã§æž¡ã
PSOP=(-A -F $'\t' -t)
"${PSQL[@]}" "${PSOP[@]}" -f "filename.sql"éèŠãã€ã³ã
â `${PSQL[@]}`
â `"${PSQL[@]}"`
â `${PSOP[@]}`
â `"${PSOP[@]}"`
ããã ãã§ã`$'\t'` ãæœ°ããã«æž¡ãããã«ãªããŸãã
5. åå â¡ïŒ`.psqlrc` ãåºå圢åŒãäžæžãããŠãã
ããã²ãšã€ã®åå ãšããŠãpsqlãèµ·åæã«èªãèšå®ãã¡ã€ã«ããããŸãã
`~/.psqlrc`
ããã« `\pset format aligned` ãªã©ã®èšå®ãå
¥ã£ãŠãããšã
ã³ãã³ãã©ã€ã³ã§ `-A` `-t` ãä»ããŠããæåãæ··ãã£ãŠãå¹ããŠãªãããã«èŠãããããšããããŸãã
â 解決çïŒ`-X` ãä»ãã
`-X` ã¯ã
psqlrcïŒãŠãŒã¶ãŒèšå®ïŒãèªã¿èŸŒãŸãªã
ãªãã·ã§ã³ã§ãã
ãªã®ã§ `PSOP` ã« `-X` ãè¶³ããšãçŽ ã®psqlãã§åãããŸãã
PSOP=(-X -A -F $'\t' -t)
"${PSQL[@]}" "${PSOP[@]}" -f "filename.sql"6. ãŸã㯠`-f` ã䜿ããããã®å Žã§ãåäœç¢ºèªããïŒåãåãïŒ
SQLãã¡ã€ã«ã®å 容ããSQLåŽã® `\pset` èšå®ã®åœ±é¿ãé¿ããããšãã¯ã`-c` ã䜿ããšæ©ãã§ãã
PSOP=(-X -A -F $'\t' -t)
"${PSQL[@]}" "${PSOP[@]}" -c "select 1 as a, 2 as b;"æåŸ ããåºåã®ã€ã¡ãŒãžïŒ
`1<TAB>2` ã®ããã« ã¿ãåºåã
äœèšãªçœ«ç·ãããããç¡ãïŒ`-t -A` ã®å¹æïŒ
7. å®æåœ¢ïŒãã®ãŸãŸã³ããçšïŒ
ãå®å šããå£ãã«ããããpsqlrcã®åœ±é¿ãæé€ããåªå ãããªãããã
#!/usr/bin/env bash
set -e
PSQL=(psql -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME")
PSOP=(-X -A -F $'\t' -t)
"${PSQL[@]}" "${PSOP[@]}" -f "filename.sql"8. ãããããã¹éïŒãã§ãã¯ãªã¹ãïŒ
â é åå±éãã¯ã©ãŒãããŠãããïŒ
`"${PSQL[@]}"` / `"${PSOP[@]}"` ã«ãªã£ãŠããã
â `$'\t'` ã䜿ãæãæªã¯ã©ãŒãå±éããŠããªããïŒ
ã¯ã©ãŒãç¡ãã ãšã¿ããåå²ããããã
â `.psqlrc` ã®åœ±é¿ããªããïŒ
`-X` ãä»ãããšåãåãã§ãã
`ls -la ~/.psqlrc` ã§ååšç¢ºèª
â ãªãã·ã§ã³ã®äœçœ®ã¯é©åãïŒ
åºæ¬ã¯ ãªãã·ã§ã³ â `-f` ã®é ãåããããã
ãŸãšã
é åã¯å¿ ã `"${arr[@]}"` ã§æž¡ãïŒæéèŠïŒ
`-F $'\t'` ã¯ã¿ãå®äœãªã®ã§ãæªã¯ã©ãŒãå±éã§å£ãããã
`.psqlrc` ãæ··ãããšãå¹ããŠãªãããã«èŠãããâ `-X` ã§ç¡èŠã§ãã
ã§ã¯
ãŸãïŒ
éå»ã®èšäº
- #ã¹ã¿ãŒãã¢ãã
- #å人éçº
- #åå¿é²
- #ITãšã³ãžãã¢
- #Mac
- #ã·ã¹ãã ãšã³ãžãã¢
- #linux
- #ã€ã³ãã©ãšã³ãžãã¢
- #ãã©ãã«ã·ã¥ãŒãã£ã³ã°
- #ã¯ã©ãŠããµãŒãã¹
- #é å
- #PostgreSQL
- #cli
- #ã³ãã³ãã©ã€ã³
- #BASH
- #ã·ã§ã«ã¹ã¯ãªãã
- #ã€ã³ãã©æ§ç¯
- #éçºã¡ã¢
- #ãšã³ãžãã¢ã®åŠã³
- #éçšèªåå
- #å®è£ èšé²
- #psql
- #psqlrc