PostgreSQL pgbash

[Home] [Download] [Install] [Usage] [Example] [FAQ]

Examples of Pgbash

Examples such as the connection of the database, the output of the retrieval result, the output of the database information and the shell script are shown.

Example of SQL in the interactive environment
Example of a shell script
Example of a CGI program
Connection example of the database
Output example of the retrieval result
Output example of the database information
Example of use of the SQL shell variable

Example of SQL in the interactive environment

prompt> /usr/local/bin/pgbash
Welcome to the PGBASH version 2.1 (bash-2.03)

  Type '[time] SQL; [pipeline][redirection][&]' to execute SQL.
  Type 'exec_sql [option] ["SQL"]' to execute SQL with options.
  Type '?' for HELP. (This help is defined with ~/.pgbashrc)

pgbash> ? 
  ?       : this help
  h  [SQL]: help <SQL> syntax or all SQL_commands
  o       : help OPTIONs of 'exec_sql' function
  v       : print PGBASH VERSION
  s       : print STATUS after SQL execution
  u       : list USERs
  l       : list DATABASEs
  m       : list CONNECTIONs
  d  [TBL]: list tables,indices,columns in <TaBLe>, or all the tables
  dt      : list TABLEs
  di      : list INDEXes
  ds      : list SEQUENCEs
  dg      : list GRANT/REVOKE permissions
  dA      : list AGGREGATES
  dD [obj]: list DESCRIPTIONs for <TAble,FIeld,TYp,FU,OP>, or all
  dF      : list PosgreSQL FUNCTIONs
  dO      : list OPERATORs
  dS      : list SYSTEM tables and indexes
  dT      : list TYPEs

pgbash> h 
# HELP: Command Name List:
    abort transaction        alter table              alter user
    begin work               cluster                  close
    commit work              connect                  disconnect
 (SNIP)

pgbash> h "create table" 
# SQL command: create table
  Description: create a new table
  Syntax :
        CREATE [TEMP] TABLE class_name
        (attr1 type1 [DEFAULT expression] [NOT NULL], ...attrN
        [[CONSTRAINT name] CHECK condition1, ...conditionN] )
        [INHERITS (class_name1, ...class_nameN)
;
pgbash> h errno 
# SQL error code:
    SQL_OK                 :    0 : normal end.
    SQL_NOT_FOUND          :  100 : EOF(End Of File).
    SQL_SYSTEM_ERROR       : -200 : system error.
    SQL_TOO_MANY_ARGUMENTS : -201 : too many arguments in fetch_stmt.
    SQL_TOO_FEW_ARGUMENTS  : -202 : too few  arguments in fetch_stmt.
    SQL_CONNECT_ERROR      : -203 : database connection error.
 (SNIP)
 
pgbash> o 
# Option:
    -i        set CGI mode and set DATA received from WWW server
    -h item   help item: select,insert,.. or next command:
              [HElp,ALl,OPtion,ERrno,EXample]<2 char are judged>
    -v        display VERSION of pgbash(exec_sql)
    -s        display SQLCA(SQLCODE,etc..) (after SQL execution)
 (SNIP)

pgbash> v 
# PostgreSQL 7.0.0 on i386-unknown-freebsd2.2.7, compiled by gcc 2.7.2.1
# PGBASH version 2.1 (bash-2.03)

pgbash> u 
+----------+------------+----------+
| Username | SupperUser | CreateDB |
+----------+------------+----------+
| postgres | t          | t        |
| admin    | f          | t        |
| pgbash   | f          | t        |
+----------+------------+----------+
(3 rows)

pgbash> l 
# Databases list
+-----------+----------+-----------+
| Database  | Owner    | Encoding  |
+-----------+----------+-----------+
| admin     | admin    | EUC_JP    |
| pgbash    | pgbash   | SQL_ASCII |
| postgres  | postgres | SQL_ASCII |
| template1 | postgres | SQL_ASCII |
+-----------+----------+-----------+
(4 rows)

pgbash> connect to admin@red.psn.ne.jp user admin NULL;
# PostgreSQL 6.5.1 on i686-pc-linux-gnu, compiled by gcc 2.95.1
# CONNECT TO  admin@red.xxx.ne.jp:5432  AS  admin  USER  admin

pgbash> connect to postgres@red.psn.ne.jp user postgres NULL; 
# PostgreSQL 6.5.1 on i686-pc-linux-gnu, compiled by gcc 2.95.1
# CONNECT TO  postgres@red.xxx.ne.jp:5432  AS  postgres  USER  postgres

pgbash> m 
# Connected Databases List (C: current database is '*')
+---+--------------+-----------+-------------------------------+
| C | connect_name | user_name | target_name(dbname@host:port) |
+---+--------------+-----------+-------------------------------+
|   | admin        | admin     | admin@red.psn.ne.jp:5432      |
| * | postgres     | postgres  | postgres@red.psn.ne.jp:5432   |
+---+--------------+-----------+-------------------------------+
(2 rows)

pgbash> dt 
# Database = postgres
+----------+--------------+-------+
| Owner    | Relation     | Type  |
+----------+--------------+-------+
| postgres | pgbash_test  | table |
| postgres | pgbash_test2 | table |
+----------+--------------+-------+

pgbash> create table test (
> code int not null primary key,
> name char(24),
> addr varchar(32) ); 
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'test_pkey' for table 'test'
CREATE

pgbash> insert into test values (1001,'sakaida','kobe'); 
INSERT 551086 1
pgbash> insert into test values
> (1002,'personal','osaka'); 
INSERT 551087 1

pgbash> select * from test; 
code|name                    |addr
----+------------------------+-----
1001|sakaida                 |kobe
1002|personal                |osaka
(2 rows)

pgbash> s 
# SQL status (shell variable)
  SQLCODE   = 0         (SQL error code)
  SQLNTUPLE = 2         (number of tuples)
  SQLNFIELD = 3         (number of fields)
  SQLERRML  = 0         (length of SQLERRMC)
  SQLERRMC  =

pgbash> d test 
# Table   = test
+-------+---------------+--------+
| Filed | Type          | Length |
+-------+---------------+--------+
| code  | int4 not null |      4 |
| name  | char()        |     24 |
| addr  | varchar()     |     32 |
+-------+---------------+--------+
(3 rows)

+-----------+
| index     |
+-----------+
| test_pkey |
+-----------+
(1 row)

pgbash> select aa,bb from test; 
(-403)ERROR:  attribute 'aa' not found
pgbash> s 
# SQL status (shell variable)
  SQLCODE   = -403      (SQL error code)
  SQLNTUPLE = 0         (number of tuples)
  SQLNFIELD = 0         (number of fields)
  SQLERRML  = 33        (length of SQLERRMC)
  SQLERRMC  = ERROR:  attribute 'aa' not found

Example of a shell script

#!/usr/local/bin/pgbash   
source ~/.pgbashrc    ..... the case in which ~/.pgbashrc is used.
CONNECT TO postgres; 
CONNECT TO dbname2 as db2 user postgres; 
SET CONNECTION default;
m
exec_sql -N '-NULL-' -Z '-0-'  "SELECT * FROM test  "
exec_sql -d db2 -O 'BORDER=0'  "SELECT * FROM test2 "
begin;
if exec_sql "declare cur cursor for select * from test"
then
   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]} .."
#
exec_sql -e -l database -l table -l index -p test
DISCONNECT ALL;

Example of the CGI program


<FORM METHOD=POST ACTION=insert_table.cgi>
Code    
        <INPUT TYPE=TEXT name=CODE size=11>
Name    
        <INPUT TYPE=TEXT name=MNAME size=25>
E_mail  
        <INPUT TYPE=TEXT name=E_MAIL    size=33>

Password  
        <INPUT TYPE=password NAME=passwd>

#!/usr/local/bin/pgbash
exec 2>&1
echo "Content-type: text/html"
echo ""
exec_sql -i
connect to admin as db1 user admin $passwd; 
Insert into Member values($CODE,'$NAME','$E_MAIL');

Connection example of the database

  CONNECT TO default;
  CONNECT TO dbname@www.psn.co.jp as db1 user admin;
  CONNECT TO postgres as db2;
  exec_sql -mL
  # Connected Databases List (C: current database is '*')
  +---+--------------+-----------+-------------------------------+
  | C | connect_name | user_name | target_name(dbname@host:port) |
  +---+--------------+-----------+-------------------------------+
  |   | _DEFAULT_    | postgres  | postgres:5432                 |
  |   | db1          | admin     | dbname@www.psn.co.jp:5432     |
  | * | db2          | postgres  | postgres:5432                 |
  +---+--------------+-----------+-------------------------------+
  (3 rows)
  
  SET CONNECTION db1;
  exec_sql -mL
  +---+--------------+-----------+-------------------------------+
  | C | connect_name | user_name | target_name(dbname@host:port) |
  +---+--------------+-----------+-------------------------------+
  |   | _DEFAULT_    | postgres  | postgres:5432                 |
  | * | db1          | admin     | dbname@www.psn.co.jp:5432     |
  |   | db2          | postgres  | postgres:5432                 |
  +---+--------------+-----------+-------------------------------+
  (3 rows)

  DISCONNECT db2;
  +---+--------------+-----------+-------------------------------+
  | C | connect_name | user_name | target_name(dbname@host:port) |
  +---+--------------+-----------+-------------------------------+
  |   | _DEFAULT_    | postgres  | postgres:5432                 |
  | * | db1          | admin     | dbname@www.psn.co.jp:5432     |
  +---+--------------+-----------+-------------------------------+
  (2 rows)

Output example of the retrieval result

select * from test ; 
code|name      |address
----+----------+--------
111 |sakaida   |kobe
222 |haruhiko  |hirosima
333 |nobu      |
    |          |osaka
(4 rows)

exec_sql -N '-NULL-' -Z '-0-' "select * from test"
code  |name      |address
------+----------+--------
111   |sakaida   |kobe
222   |haruhiko  |hirosima
333   |nobu      |-0-
-NULL-|          |osaka
(4 rows)

exec_sql -L -C 'TEST TABLE' -N '-NULL-' -Z '-0-' "select * from test"
TEST TABLE
+--------+-----------+----------+
| code   | name      | address  |
+--------+-----------+----------+
| 111    | sakaida   | kobe     |
| 222    | haruhiko  | hirosima |
| 333    | nobu      | -0-      |
| -NULL- |           | osaka    |
+--------+-----------+----------+
(4 rows)

exec_sql -H -C 'TEST TABLE' -N '-NULL-' -Z '-0-' "select * from test"

TEST TABLE
code	name	address
111	sakaida	kobe
222	haruhiko	hiroshima
333	nobu	-0-
-NULL-		osaka

Output example of the database information

exec_sql -l database
# Databases list

Database |Owner   |Encoding
---------+--------+---------
admin    |admin   |EUC_JP
pgbash   |pgbash  |SQL_ASCII
postgres |postgres|SQL_ASCII
template1|postgres|SQL_ASCII
(4 rows)

exec_sql -H -C 'MY DATABASE' -l database 
MY DATABASE
MY DATABASE
Database Owner Encoding
admin admin EUC_JP
pgbash pgbash SQL_ASCII
postgres postgres SQL_ASCII
template1 postgres SQL_ASCII 

(4 rows)


exec_sql -L -l table -l index 
# Database = postgres
+----------+------------+-------+
| Owner    | Relation   | Type  |
+----------+------------+-------+
| postgres | bash_test  | table |
| postgres | bash_test2 | table |
| postgres | test       | table |
| postgres | test2      | table |
+----------+------------+-------+
(4 rows)

# Database = postgres
+----------+----------+-------+
| Owner    | Relation | Type  |
+----------+----------+-------+
| postgres | id_ind   | index |
| postgres | vr_ind   | index |
+----------+----------+-------+
(2 rows)

Example of use of the SQL shell variable

$SQLCODE shell variable

#!/usr/local/bin/pgbash
begin;
declare cur cursor for select * from test;
fetch in cur into :_AA :_AA_IND, :_BB ;
if(( SQLCODE == 0 && _AA_IND != SQL_NULL )); then
   echo "$_AA, $_BB, $_CC"
fi
end;

$SQLNFILED and ${SQLFILEDNAME[i]} shell variables

#!/usr/local/bin/pgbash
select * from test;
echo "nFields=$SQLNFIELD"
declares -i x; let x=0; while(( x < SQLNFIELD ))
do
    echo -n "${SQLFIELDNAME[x]}  "
    let x=x+1
done
echo " "

$SQLOID shell variable

insert into test values(123,'aaa','bbb');
OID = $SQLOID
select * from test where oid=$OID;

[Home] [Download] [Install] [Usage] [Example] [FAQ]

Last Modified at 2000/01/07