Examples

Usage of PostgreSQL COPY statements.

import asyncdispatch, asyncpg, strutils

proc makeCopyTo(conn: apgConnection) {.async.} =
  # For simple COPY operation there 2 delimeters:
  # '\t' - column delimeter
  # '\l' - row delimeter
  var copyString = "text1\t1\t2\ltext2\t2\t3\ltext3\t4\t5"

  # Drop existing table `foo`.
  var dr = await conn.exec("DROP TABLE IF EXISTS foo;")
  # Close result object
  close(dr)

  # Create new table `foo` with columns (a:text, b:int8, c:int8)
  var ar = await conn.exec("CREATE TABLE foo(a text, b int8, c int8);")
  # Close result object
  close(ar)

  # Start copy operation
  var sr1 = await conn.exec("COPY foo FROM stdin;")
  # Close result object
  close(sr1)

  # Send data from copyString
  var cr1 = await conn.copyTo(cast[pointer](addr copyString[0]),
                              len(copyString).int32)
  # Close result object
  close(cr1)

  # Send EOF to finish copy operation
  var cr2 = await conn.copyTo(nil, 0)
  # Echo number of rows inserted during copy operation
  echo getAffectedRows(cr2[0])
  # Close result object
  close(cr2)

proc makeCopyFrom(conn: apgConnection) {.async.} =
  # String buffer to receive data from copy operation
  var inputString = newString(80)

  # Start copy operation
  var ar = await conn.exec("COPY foo TO stdout;")
  # Close result object
  close(ar)

  var idx = 0
  while true:
    var rr = await conn.copyFromInto(cast[pointer](addr inputString[idx]),
                                     len(inputString))
    if rr == 0:
      # EOF received
      inputString.setLen(idx)
      break
    else:
      idx += rr

  # Echo copy result
  echo(inputString)

  # Drop table `foo`
  var dr = await conn.exec("DROP TABLE foo")
  close(dr)

# Prepare connection string
var connStr = "host=localhost port=5432 dbname=travis_ci_test user=postgres"
# Establish connection to PostgreSQL server
var conn = waitFor connect(connStr)

# Perform `COPY FROM` operation
waitFor conn.makeCopyTo()
# Perform `COPY TO` operation
waitFor conn.makeCopyFrom()

# Close connection
conn.close()