Powershell で CSVから Postgres用の Upsert Sql を生成する

使い方

#CSVを読み込む
$c = Get-Content ".\sample.csv"

#テーブル名
$tableName = "work.sample_table"
#テーブルのキー
$tableKey = "sample_pkey"

# 1行目（0番目)＝ヘッダを取り出す
[string]$sHeaders = $c[0]

# 2行目以降(1番目以降) がデータなのでループしてSQL生成。
for($k=1; $k -lt $c.Length; $k++){
  [string]$sSql = BuildUpsertSqlString -sTable $tableName -sTableKey $tableKey -sHeaders $sHeaders -sValues $c[$k]

#  ...
# sqlを使った処理をここに書く
#  ...

}

BuildUpsertSqlString.ps1

function BuildUpsertSqlString
{
  param (
    [Parameter(Mandatory=$true)][string]$sTable,
    [Parameter(Mandatory=$true)][string]$sTableKey,
    [Parameter(Mandatory=$true)][string]$sHeaders,
    [Parameter(Mandatory=$true)][string]$sValues
  )

  [string[]]$aHeaders = @()
  [string]$sUpdValues = ""

  # double quoted
  $sHeaders = dquote -str $sHeaders

  # single quoted
  $sValues = squote -str $sValues

  # Build ON CONFLICT Values
  $aHeaders = $sHeaders -split ","
  for($i=0; $i -lt $aHeaders.Length; $i++ ){
    $aHeaders[$i] = $aHeaders[$i] + " = EXCLUDED." + $aHeaders[$i]
  }
  $sUpdValues = $aHeaders -join ", "

  $sSql = "INSERT INTO _TABLE_(_HEADERS_) VALUES(_VALUES_) ON CONFLICT ON CONSTRAINT _KEY_ DO UPDATE SET _UPDVALUES_ ;"

  $sSql = $sSql -replace "_TABLE_" , $sTable
  $sSql = $sSql -replace "_HEADERS_" , $sHeaders
  $sSql = $sSql -replace "_VALUES_" , $sValues
  $sSql = $sSql -replace "_KEY_" , $sTableKey
  $sSql = $sSql -replace "_UPDVALUES_" , $sUpdValues

  Write-Verbose $sSql
  return $sSql
}

# Double Quote
function dquote
{
  param(
    [string]$str
  )
  [string[]]$arr = $str -split ","
  for($i=0; $i -lt $arr.Length; $i++ ){
    $arr[$i] = '"' + $arr[$i] + '"'
  }
  $str = $arr -join ","
  return $str
}

# Single Quote
function squote
{
  param(
    [string]$str
  )
  [string[]]$arr = $str -split ","
  for($i=0; $i -lt $arr.Length; $i++ ){
    if($arr[$i] -ne ""){
      $arr[$i] = "'" + $arr[$i] + "'"
    }else{
      $arr[$i] = "NULL"
    }
  }
  $str = $arr -join ","
  return $str
}