Using open database connection to PostgreSQL in BASH?

Question

I have to use BASH to connect to our PostgreSQL 9.1 database server to execute various SQL statements.

We have a performance issue caused by repeatedly opening/closing too many database connections (right now, we send each statement to a psql command).

I am looking at the possibility of maintaining an open database connection for a block of SQL statements using named pipes.

The problem I have is that once I open a connection and execute a SQL statement, I don't know when to stop reading from the psql. I've thought about parsing the output to look for a prompt, although I don't know if that is safe considering the possibility that the character may be embedded in a SELECT output.

Does anyone have a suggestion?

Here's a simplified example of what I have thus far...

#!/bin/bash

PIPE_IN=/tmp/pipe.in
PIPE_OUT=/tmp/pipe.out

mkfifo $PIPE_IN $PIPE_OUT
psql -A -t jkim_edr_md_xxx_db < $PIPE_IN > $PIPE_OUT &
exec 5> $PIPE_IN; rm -f $PIPE_IN
exec 4< $PIPE_OUT; rm -f $PIPE_OUT

echo 'SELECT * FROM some_table' >&5

# unfortunately, this loop blocks
while read -u 4 LINE
do
    echo LINE=$LINE
done

Answer 1

Use --file=filename for a batch execution.

Depending on your need for flow control you may want to use another language with a more flexible DB API (Python would be my choice here but use whatever works).