pgbash-2.1 released

Hi,

Please let me report that pgbash-2.1 was released.

(Pgbash is a tool which can execute SQL directly with using all
the functions of a bash shell. And Pgbash has functionality
which is similar to psql.)

* Download Page
http://www.psn.co.jp/PostgreSQL/pgbash/download-e.html

* Example in interactive environment

-------------------
prompt> /usr/local/bin/pgbash .....Start of pgbash
pgbash> connect to db2(at)xxx(dot)com user sakaida; .....connect to database
# PostgreSQL 7.0.0 on i386-unknown-freebsd2.2.x, compiled by gcc 2.7.x
# CONNECT TO pgbash:5432 AS pgbash USER pgbash

pgbash> select * from test limit 100; | more .....pipeline
code|name |addr
----+----------------+--------
1|sakaida |kobe
2|yumiko |kyoto
(2 rows)
pgbash> select * from test; > /tmp/sel.dat & .....redirect+background_Job
pgbash> addr='Osaka'
pgbash> insert into test values(
> 111,'name', .....new line
> '$addr' .....use shell variable
> );
INSERT 22144 1
pgbash> connect to db3(at)yyy(dot)com user postgres; > /dev/null
pgbash> set connection db2; .....set current D/B to db2
pgbash> select * from test; &> /tmp/test.dat'.....select db2's test
pgbash> ls /tmp
test.dat

pgbash> exec_sql -d db3 "select * from test3".....change connection to db3
pgbash> begin;
BEGIN
pgbash> declare cur cursor for select * from test;
pgbash> fetch in cur into :AA,:BB; .....set shell variables
pgbash> echo "code=$AA, name=$BB"
code=1, name=sakaida
pgbash> !fetch .....FETCH again
pgbash> !echo .....echo again
code=2, name=yumiko
pgbash> end;
COMMIT
pgbash> disconnect all .....disconnect all connections
pgbash> exit .....End of pgbash
prompt>
--------------------

* Example of a shell script

--------------------
#!/usr/local/bin/pgbash
CONNECT TO postgres;
CONNECT TO dbname2 as db2 user postgres;
SET CONNECTION default;
SELECT * FROM test;
exec_sql -d db2 "SELECT * FROM test2"
#
echo "key data="
read keydata
#
BEGIN;
DECLARE cur CURSOR FOR SELECT * FROM test WHERE col1= $keydata;
declares -i x; let x=0; while(( x < 100 ))
do
FETCH IN cur INTO :_AA :_AA_IND, :_BB ;
if((SQLCODE==SQL_NOT_FOUND)); then
let x=101
else
if(( SQLCODE == 0 && _AA_IND != SQL_NULL )); then
echo "$_AA, $_BB, $_CC"
fi
fi
let x = x+1
done
fi
END;
echo "nFields=$SQLNFIELD FieldName=${SQLFIELDNAME[0]} .."
#
DISCONNECT ALL;
--------------------

* Features

1. Pgbash can talk to 6.5/7.0 backend.

Pgbash(+ 6.5-libpq) can talk to both 6.5 and 7.0 backend.
Pgbash(+ 7.0-libpq) can talk to both 6.5 and 7.0 backend.
Pgbash(+ non-MB-libpq) can talk to both non-MB and MB backend.
Pgbash(+ MB-libpq) can talk to both non-MB and MB backend.

Here, "MB" is MULTIBYTE.

2. It is possible that pgbash manipulates multiple databases using
CONNECT, DISCONNECT and SET CONNECTION (or -d option ).

3. Pgbash has functionality that sets the retrieval result into
the shell variable using FETCH INTO statement.

4. Pgbash has a COPY function which a general user can specify
column names and a file name.
(ex. COPY table( col1,.. ) TO '/tmp/test.dat')

5. It is possible to set CGI mode. In CGI mode, Pgbash switches
the output to HTML, and read the data by GET/POST method, and
read the data of HTTP_COOKIE.

6. Pgbash sets "error code", "error message", "number of tuples",
etc into the shell variable. Therefore, it is possible to know
the condition after the SQL execution.

--
Regards,
SAKAIDA Masaaki -- Osaka, Japan
# I am not good at English ;-)