Build UPSERT (INSERT OR UPDATE ) SQL for PostgreSQL

BuildUpsertSql.vb

Sub test___BuildUpsertSql()

  Dim res As String
  res = BuildUpsertSql("sample_table", _
                                    "sample_pkey", _
                                    "user_name, user_email, user_address", _
                                    "test_user, test@example.jp, Tokyo")
  Debug.Print res
  '#   INSERT INTO sample_table(
  '#    user_name, user_email, user_address
  '#   )
  '#   VALUES (
  '#    test_user, test@example.jp, Tokyo
  '#   )
  '#   ON CONFLICT ON CONSTRAINT sample_pkey DO UPDATE
  '#   SET  user_name = EXCLUDED.user_name
  '#    ,  user_email = EXCLUDED. user_email
  '#    ,  user_address = EXCLUDED. user_address
End Sub


'#-----------------------------------------------------------------------------
'# Build  INSERT OR UPDATE SQL  for PostgreSQL
'#-----------------------------------------------------------------------------
Function BuildUpsertSql(sTable As String, _
                                      sTableKey As String, _
                                      sHeaders As String, _
                                      sValues As String) As String

  Dim aHeaders() As String
  Dim sUpdValues As String

  aHeaders = Split(sHeaders, ",")
  sUpdValues = ""

  '# head: | a | b | c | d |
  '# =>    a = EXCLUDED.a , b = EXCLUDED.b , c = EXCLUDED.c , d = EXCLUDED.d
  Dim h As Long
  For h = LBound(aHeaders) To UBound(aHeaders)
    If h <> LBound(aHeaders) Then sUpdValues = sUpdValues & "  ,"
    sUpdValues = sUpdValues & " " & aHeaders(h) & " = EXCLUDED." & aHeaders(h) & vbCrLf
  Next

  Dim sSql As String
  sSql = " INSERT INTO $_TABLE_$( " & vbCrLf
  sSql = sSql & "  $_HEADERS_$ " & vbCrLf
  sSql = sSql & " )" & vbCrLf
  sSql = sSql & " VALUES (" & vbCrLf
  sSql = sSql & "  $_VALUES_$ " & vbCrLf
  sSql = sSql & " )" & vbCrLf
  sSql = sSql & " ON CONFLICT ON CONSTRAINT $_KEY_$ DO UPDATE " & vbCrLf
  sSql = sSql & " SET " & sUpdValues

  sSql = Replace(sSql, "$_TABLE_$", sTable)
  sSql = Replace(sSql, "$_HEADERS_$", sHeaders)
  sSql = Replace(sSql, "$_VALUES_$", sValues)
  sSql = Replace(sSql, "$_KEY_$", sTableKey)

  BuildUpsertSql = sSql
End Function