Cacher is the code snippet organizer for pro developers

exec_sql () {
  # Execute an arbitrary SQL statement.
  local cmd="$1"

  local -i exit_status=0
  psql --host="$db_host" --dbname="$db_name" --no-psqlrc --file=- \
    <<<"$cmd" || {
      exit_status="$?"
      printf "%s: psql failed on:\n%s\n" "$FUNCNAME" "$cmd" >&2
      return "$exit_status"
    }
}

file_is_processed () {
  # Determine if a claim file has been processed.
  local file_name="$1"

  local cmd=
  read -r -d '' cmd <<sql
$psql_cmd_env
\set file_class_sym $file_class_sym
\set file_name $file_name
SELECT CASE COUNT(*) WHEN 0 THEN 0 ELSE 1 END
  FROM files
  WHERE file_class_sym = :'file_class_sym' AND
    file_name = :'file_name';
sql

  local stdout=
  stdout="$(exec_sql "$cmd")" || {
    printf "%s: Failed on file '%s'.\n" \
      "$FUNCNAME" "$file_name" >&2
    return 2
  }

  # If count is 0, return false (1). Otherwise, true (0).
  [[ $stdout != 0 ]]
}

mark_file_processed () {
  # Mark a claim file processed.
  local file_name="$1"

  local cmd=""
  read -r -d '' cmd <<sql
$psql_cmd_env
\set file_class_sym $file_class_sym
\set file_name $file_name
INSERT INTO files (file_class_sym, file_name)
  VALUES (:'file_class_sym', :'file_name');
sql

  exec_sql "$cmd" || {
    printf "%s: Failed on file '%s'.\n" \
      "$FUNCNAME" "$file" >&2
    return 1
  }
}

read -r -d '' psql_cmd_env <<sql
\set QUIET
\set ON_ERROR_STOP
\pset format unaligned
\pset tuples_only on
SET search_path = $db_schema;
sql