pgbash-2.1 released - Mailing list pgsql-interfaces

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@xxx.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@yyy.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+1done
 
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
possibleto know   the condition after the SQL execution. 
 

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