pgbashWriting PostgreSQL-enabled Shell Scripts

pgbash is a set of PostgreSQL-related enhancements for the bash shell. pgbash turns the bash shell into a PostgreSQL client application. When you add pgbash to the bash shell, you can execute PostgreSQL commands without invoking psql (or some other PostgreSQL client application) firstinstead of invoking a separate program to execute SQL commands, the pgbash shell interacts directly with the database using the libpq client library. At the time that we are writing this chapter, pgbash is distributed as a set of source-code patches for bash version 2.05athe current version of bash is 3.0 so pgbash is a few revisions out of sync. Hopefully by the time you read this, pgbash will work with bash version 3.0. The pgbash web site states that pgbash works with PostgreSQL versions 7.3 and aboveit works very well with PostgreSQL version 8.0.

When you start a pgbash session, you're running a complete bash shell so you can use any of the bash features that you know and love: redirection, control structures (if/then/else, do/while, ...), filename completion, brace expansion, and so on. pgbash adds a few built-ins to the bash shell. For example, to connect to the movies database and execute a simple query:

$ pgbash
Welcome to Pgbash version 7.3 ( bash-2.05a.0(2)-release )
 Type '?' for HELP.
 Type 'connect to DB;' before executing SQL.
 Type 'SQL;' to execute SQL.
 Type 'exit' or 'Ctrl+D' to terminate Pgbash.

pgbash> CONNECT TO movies;
# PostgreSQL 8.0.0 on i686-pc-linux-gnu
# CONNECT TO movies:5432 AS movies USER bruce

pgbash> SELECT * FROM customers;
 customer_id | customer_name | phone | birth_date | balance
-------------+----------------------+----------+------------+--------
 2 | Rubin, William | 555-2211 | 1972-07-10 | 15.00
 1 | Jones, Henry | 555-1212 | 1970-10-10 | 16.00
 4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 | 3.00
 3 | Panky, Henry | 555-1835 | 1968-01-21 | 16.16
(4 rows)

You may have noticed that I've included a semicolon at the end of each command. pgbash modifies the bash parser to add direct support for SQL commandsto find the end of an SQL command, pgbash looks for a semicolon.

When you execute a CONNECT TO command, pgbash connects to the database that you specify and assigns a symbolic name to that connection. By default, pgbash creates a connection whose name matches the name of the database that you connect to, but you can assign your own name to the connection by including an AS connectionName clause. For example:

pgbash> CONNECT TO movies AS movies1;
pgbash> CONNECT TO movies AS movies2;

CONNECT TO { database[@host[:port]] | DEFAULT }
 [AS connectionName]
 [USER userName
 [[{IDENTIFIED BY | USING | /}] password]];

Unlike psql, a single pgbash session can maintain any number of open database connections. You can switch from connection to connection by executing SET CONNECTION TO connectionName command. When you switch to a new connection, pgbash does not close the previous connection.

The first command creates a connection to the test database. The second command creates a connection to the movies database. When you execute the second CONNECT command, pgbash simply opens a second connection but it does not close the test connection. You can see a list of open connections with the ?m command:

pgbash> ?m
# Connected Databases List (C: current database is '*')
+---+--------------+-----------+---------------------------+-----------------+
| C | connect_name | user_name | target_name(db@host:port) | client_encoding |
+---+--------------+-----------+---------------------------+-----------------+
| * | movies | korry | movies:5432 | |
| | test | korry | test:5432 | |
+---+--------------+-----------+---------------------------+-----------------+
(2 rows)

Notice the * in the first column? That tells you which connection is activewhen you execute an SQL command, pgbash sends the command to the server on the other end of the active connection. You can change the active connection by executing the SET CONNECTION connectionName command:

pgbash> SET CONNECTION TO test;
pgbash> ?m
# Connected Databases List (C: current database is '*')
+---+--------------+-----------+---------------------------+-----------------+
| C | connect_name | user_name | target_name(db@host:port) | client_encoding |
+---+--------------+-----------+---------------------------+-----------------+
| | movies | korry | movies:5432 | |
| * | test | korry | test:5432 | |
+---+--------------+-----------+---------------------------+-----------------+
(2 rows)

If you open more than one connection, you can interact with many databases at the same time. For example, to copy a table (customers) from one database to another[1]:

[1] If the COPY command shown here reports an error message (pgbash: select: command not found), you have an older version of pgbash and you'll need to execute pipelined commands using bash's builtin syntax, like this: COPY customers TO STDOUT; | (exec_sql "SET CONNECTION test"; exec_sql "COPY customers FROM STDIN" ).

pgbash> CONNECT TO test;
# PostgreSQL 8.0.0 on i686-pc-linux-gnu
# CONNECT TO test:5432 AS movies USER bruce

pgbash> CONNECT TO movies;
# PostgreSQL 8.0.0 on i686-pc-linux-gnu
# CONNECT TO movies:5432 AS movies USER bruce

pgbash> COPY customers TO STDOUT; |
> (SET CONNECTION test; && COPY customers FROM STDIN; )

After opening two connections (one connected to the test database and one connected to the movies database), the last command copies the customers table (from the movies database) to STDOUT, pipes the result to a subshell that switches to the test database, and then copies the customers table from STDIN. You can redirect an SQL command's output stream to another process (using the | operator) or to a file (using the > or >> operators). You can also redirect the standard input stream of the COPY command (using the < or | operators).

To close a connection, execute the command DISCONNECT connectionName.

The benefits offered by pgbash become more apparent when you need to interact with a facility outside of the database. For example, say you have a whole directory full of photographs that you want to load into a PostgreSQL database. (Each photo should be stored as a large-object.) I'll create a table named wallpapers to hold the photos:

pgbash> CREATE TABLE wallpapers ( name VARCHAR, lo_oid );
CREATE TABLE

Importing a whole collection of large-objects is not an easy task with most PostgreSQL client applications, but it's trivial with pgbash. The following snippet of code imports all image files found in /usr/share/wallpapers:

pgbash> FOR fi IN /usr/share/wallpapers/*.jpg
> DO
> INSERT INTO wallpapers VALUES
> (
> '$(basename $fi)',
> lo_import( '$fi' )
> );
> DONE

I've mixed bash features and PostgreSQL features in this example. The FOR...IN loop iterates through each image in the directory (assigning a different filename to $fi for each trip through the loop). Inside of the loop, I'm executing an INSERT command that adds a new row during each iteration. To compute the name column, I'm using bash's command-substitution feature to extract the filename component from $fi. The lo_id column column is filled in by the call to lo_import(). It's important to realize that every INSERT command (in this example) uses the same connectionI'm not opening a new connection, adding a single row, and then closing that connection for each image. That has two important (and beneficial) implications. First, I'm not suffering any performance degradation caused by repeated connection setups and teardowns. Second, I can execute the entire loop within a single transaction. (I just need to execute a BEGIN WORK command before the loop and a COMMIT or ROLLBACK after the loop.)

The previous example showed that you can use bash control structures to control the interaction with a PostgreSQL database. You can also use a PostgreSQL cursor to control bash. For example, you may want to send an email announcing a "Movie of the Week" special to all of your video store customers. First, I'll add an email address column to the customers table and add some sample data:

pgbash> ALTER TABLE customers ADD COLUMN email VARCHAR;
ALTER TABLE

pgbash> UPDATE CUSTOMERS SET email='[e-mail]' WHERE customer_id=2;
UPDATE 1
pgbash> UPDATE CUSTOMERS SET email='[e-mail]' WHERE customer_id=3;
UPDATE 1

Now I can read through the customers table (using a cursor) and send an email to each recipient as shown in Listing 19.10.

Listing 19.10. sendMOTW.sh

 1 #!/usr/local/bin/pgbash
 2 #
 3 # Filename: sendMOTW.sh
 4 #
 5
 6 CONNECT TO movies;
 7
 8 BEGIN WORK;
 9
10 DECLARE cust CURSOR FOR SELECT customer_name, email FROM customers;
11
12 while $(true)
13 do
14
15 FETCH cust INTO :name, :email :email_ind;
16
17 if(( $SQLCODE != $SQL_OK ))
18 then
19 break
20 fi
21
22 if(( $email_ind != $SQL_NULL ))
23 then
24
25 firstName=$(echo $name | cut -d',' -f 2)
26
27 mail -s "Movie of the week" $email <

After connecting to the movies database and starting a new transaction, this script declares a cursor (named cust) that reads the customer_name and email values from the customers table. The while loop that covers lines 12 through 37 executes once for each row returned by the cursor. The FETCH you see at line 15 reads a single row from the cursor and creates three environment variables: $name, $email, and $email_ind. The environment variable names are determined by the INTO clause in the FETCH statement: The name of each variable is prefixed with a colon. Notice that the cursor (see line 10) retrieves two columns but the FETCH creates three environment variables. The first two correspond to the customer_name and email values. The last environment variable ($email_ind) is an indicator variable and it tells you whether the email address ($email) is NULL. You can declare an indicator for any value that you FETCH (or SELECT) INTO; just list the indicator variable after the value variable without a comma in between. If a value is NULL, its indicator variable is set to $SQL_NULL; otherwise, the indicator is set to 0 (see line 22).

Most of the PostgreSQL-specific pgbash commands modify the $SQLCODE environment variable to indicate success or failure. At line 17, I'm checking $SQLCODE to determine when to terminate the while loopthe FETCH command sets $SQLCODE to $SQL_OK on success and sets $SQLCODE to $SQL_NOT_FOUND when it reaches the end of the cursor. To see a list of the values you may find in $SQLCODE, execute the ??e command:

pgbash> ??e
+------------------------+-------------------------------------------+-----
| Value Name | Comment | Value
+------------------------+-------------------------------------------+-----
| SQL_OK | normal end. | 0
| SQL_NOT_FOUND | EOF(End Of File). | 100
| SQL_SYSTEM_ERROR | system error. | -200
| SQL_TOO_MANY_ARGUMENTS | too many arguments in fetch_stmt. | -201
| SQL_TOO_FEW_ARGUMENTS | too few arguments in fetch_stmt. | -202
| SQL_CONNECT_ERROR | database connection error. | -203
| SQL_INVALID_STMT | invalid statements. | -230
| SQL_READONLY_SHELLVAR | can not set read-only shell variable. | -231
| SQL_DB_NOT_OPEN | database not open. | -232
| SQL_CNAME_NOT_FOUND | connect-name not found. | -233
| SQL_CNAME_ALREADY_USE | connect-name already exist. | -234
| SQL_INVALID_COMMAND | invalid command. | -235
| SQL_INVALID_DATA | invalid data. |-236
| SQL_BAD_RESPONSE | bad response(backend maybe died). |-400
| SQL_EMPTY_QUERY | empty query (backend lost query). |-401
| SQL_CONNECTION_BAD | connection bad(disconnect backend) |-403
| SQL_FATAL_ERROR | query fatal error (SQL error on backend)|-403
| SQL_NONFATAL_ERROR | query nonfatal error(SQL error on backend)|-404
| SQL_NULL | indicator is NULL. |-1
+------------------------+-------------------------------------------+-----

 

After checking for the end of the result set, the test at line 22 ensures that the current row contains a non-NULL email address. When sendMOTW.sh finds a customer with a non-NULL email address, the code at lines 25 through 34 extracts the customer's first name and sends a message using the mail command. The body of the (admittedly uninspired) message appears in-line in the form of a bash HERE document (that's the stuff between the < and EOF markers).

pgbash is reasonably complete replacement for the more traditional psql command-line client. For more information about pgbash, see www.psn.co.jp/PostgreSQL/pgbash/index-e.html.

Part III PostgreSQL Administration