bash postgres coproc

psql_coproc.bash

#!/usr/bin/env bash

PSQL_COPROC_RESULT_SEPARATOR="^!^"

pgproc_connect() {
  coproc_var=$1; shift;
  coproc "$coproc_var" {
    psql --quiet --no-align --no-readline --tuples-only --csv "$@";
  } 2>/dev/null
}

pgproc_send() {
  coproc_var=$1; shift;
  typeset -n coproc=${coproc_var}
  echo "$@" >&${coproc[1]}
}

pgproc_send_end() {
  coproc_var=$1; shift;
  pgproc_send "$coproc_var" "\echo '$PSQL_COPROC_RESULT_SEPARATOR'"
}

pgproc_results() {
  coproc_var=$1; shift;
  typeset -n coproc=${coproc_var}
  while read -u "${coproc[0]}" line; do
    if [ "$line" == "$PSQL_COPROC_RESULT_SEPARATOR" ]; then
      return
    fi
    printf "$line\n"
  done
}

pgproc_close() {
  coproc_var=$1; shift;
  typeset -n coproc_pid=${coproc_var}_PID
  pgproc_send "$coproc_var" "\q"
  wait "$coproc_pid"
}

pgproc_query() {
  local coproc_var=$1; shift;
  local query="$1"
  shift
  for var in "$@"; do
    IFS="=" read variable value <<< $var
    pgproc_send "$coproc_var" "\set $variable $value"
  done
  pgproc_send "$coproc_var" "$query"
  pgproc_send_end "$coproc_var"
  pgproc_results "$coproc_var"
}


### Connect like this

set -e -u
# DBCONNECTION can be any value, having multiple connections with different coproc arrays *kind of* works, but bash complains
pgproc_connect DBCONNECTION service=my-database-service

# Now you can send queries to the connnection and read the results:
backend_pid=$(pgproc_query DBCONNECTION 'SELECT pg_backend_pid();')
echo "Backend PID is: ${backend_pid}"

randnum=$(pgproc_query DBCONNECTION "SELECT random();")
echo "Random number ${randnum} generated by pg backend ${backend_pid}"

# use placeholder and pass in values like this:
one=$(pgproc_query DBCONNECTION "SELECT :one;" one=1)
echo "server returned $one"

# Handle tabular output, not pretty, be careful with subshells
numbers=$(pgproc_query PRODDB "select generate_series, floor(random() * 100) from generate_series(1,10);");
for num in $numbers; do
  IFS="," read position random <<< $num
  printf "$position: $random\n"
done


pgproc_close DBCONNECTION