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()

Multiple SQL statements in one request

import asyncdispatch, asyncpg, strutils

proc multipleStatements(conn: apgConnection) {.async.} =
  # Execute multiple SELECT operations
  # Only text SQL queries are supported, you cannot separate
  # parameters from query, e.g.
  # var r = await conn.exec("SELECT $1; SELECT $2; SELECT $3", 1, 2, 3)
  var r = await conn.exec("SELECT 1; SELECT 2; SELECT 3")
  # Get number of results
  var resultsCount = len(r)
  echo "Results count = " & $resultsCount

  # Echo first result
  echo getValue(r[0])
  # Echo second result
  echo getValue(r[1])
  # Echo third result
  echo getValue(r[2])

  # Close results
  close(r)

var connStr = "host=localhost port=5432 dbname=travis_ci_test user=postgres"
var conn = waitFor connect(connStr)

waitFor conn.multipleStatements()
conn.close()

Array operations

import asyncdispatch, asyncpg, strutils

proc arraySupport(conn: apgConnection) {.async.} =
  # Array of strings
  var arr = ["January", "February", "March", "April"]
  # Execute SQL statements and concatenate two arrays.
  var r = await conn.exec("SELECT $1 || $2",
                          arr, ["May", "June", "July", "August"])
  
  # Echo string representation of result
  var value = getValue(r[0])
  echo(value)

  # Close results
  close(r)

var connStr = "host=localhost port=5432 dbname=travis_ci_test user=postgres"
var conn = waitFor connect(connStr)

waitFor conn.arraySupport()
conn.close()