# Bash notes: Doing postgres with bash


Define a `psql` bash array var like so at the script's start:

`psql=(psql --username=uname --dbname=dbname -X -A -t -c)`

And then use it to execute simple queries that don't need user input:

```sh
qry="select atomic_number, name from elements limit 1;"
select_result=$("${psql[@]}" "$qry")
IFS='|' read -r atomic_number name <<< $select_result
```

That last line will parse the stdout and then read the result into separate bash vars.

And that's assuming the the query result is just one row/line.

What if you have multiple rows? How to read and process each rows?

The query is executed in the same way. The results in the variable will be a
multiline string representing the multiple rows.

To process them row by row, you need to use `while` loop:

```sh
declare -A service_id_to_name
services_result="$("${psql[@]}" "select service_id, name from services order by service_id;")"
  while IFS='|' read -r service_id service_name; do
    service_id_to_name[$service_id]="$service_name"
    service_order+=( $service_id )
  done <<< "$services_result"
```

If you have to run a query that takes user input, then you should be careful. Don't embed it directly into the query. Use psql's `-v` flag to pass them separately:

```sh
upsert_sql=$(cat << SQL
insert into games
  (username, games_played, best_guess)
  values
  (:'username', :games_played, :best_guess)
  on conflict (username) do
  update set
    username = EXCLUDED.username,
    games_played = EXCLUDED.games_played,
    best_guess = EXCLUDED.best_guess
;
SQL
)

read -p "username?" username
read -p "games played?" games_played
games_played=$(( games_played + 0 )) # coercing string to int
read -p "best guess?" best_guess
best_guess=$(( best_guess + 0 ))

psql=(psql --username=uname --dbname=dbname -X -A -t)
"${psql[@]}" -v username="$username" -v games_played="$games_played" -v best_guess="$best_guess" <<< ${upsert_sql} > /dev/null
```

Note the psql placeholders in the `upsert_sql`. `:'username'` (string, hence the quote) and `:games_played` (int).

Also note the lack of `-c` flag in this psql var. While using `-v` you can't use `-c`, and you also have to feed in the sql via `<<<` (herestring).