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 Pgbash version 2.2 ( bash-2.05.0(1)-release )

  Type '?' for HELP. (HELP is defined in ~/.pgbashrc)
  Type 'connect to DB;' before executing SQL.
  Type 'SQL;' to execute SQL.
  Type 'exit' or 'Ctrl+D' to terminate Pgbash.

pgbash> ? 
  ?         : this help
  ??        : help pgbash usage
  ??h [SQL] : help SQL(or "SQL") syntax or all SQL reserved words
  ??e       : help SQLCODE (sql ERROR code)
  ??f       : help 'pgbashrc' FUNCTIONs for using a shell script
  ??o       : help 'exec_sql' options
  ?m        : show CONNECTION NAMEs
  ?o        : show current values of exec_sql_OPTIONs
  ?s        : show STATUS after executing SQL
  ?v        : show PostgreSQL and pgbash VERSION
  ?u        : list database USERs 
  ?l        : list DATABASEs 
  ?d  [rel] : list RELATIONs for table/view/index/sequence
  ?d{t|i|v} : list TABLEs/INDEXes/VIEWs
  ?dl       : list LARGE_OBJECTs 
  ?dp       : list PERMISSIONs
  ?ds       : list SEQUENCEs
  ?dA       : list AGGREGATEs
  ?dD [obj] : list DESCRIPTIONs for table/type/function/operator
  ?dF       : list FUNCTIONs
  ?dO       : list OPERATORs
  ?dS       : list SYSTEM_TABLEs
  ?dT       : list data_TYPEs
  E{+|-}    : turn on/off ECHO_QUERY sent to the backend
  Q{+|-}    : turn on/off QUIET_MODE for system/sql error messages
  L{+|-}    : turn on/off OUTER_FRAME_print
  T{+|-}    : turn on/off TOP_header_print
  B{+|-}    : turn on/off BOTTOM_count_print
  A{+|-}    : turn on/off ALIGNMENT of plain text
  X{+|-}    : turn on/off EXPANDED format
  H{+|-}    : turn on/off HTML format
  S+  [chr] : set SEPARATOR  (e.g. ','  ) or DEFAULT('|')
  N+  [str] : set NULL_STRING(e.g. '*N*') or DEFAULT('')
  Z+  [str] : set ZERO_STRING(e.g. '*0*') or DEFAULT('')
  _lo_import : lo_import  <'COMMENT'>
  _lo_export : lo_export  
  _lo_unlink : lo_unlink 

pgbash> ??h 

  # HELP: Command Name List:
    ABORT                    ALTER GROUP              ALTER TABLE
    ALTER USER               BEGIN                    CHECKPOINT
    CLOSE                    CLUSTER                  CONNECT
    ...

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] )
   ...

pgbash> ??e 
# 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.
    ...
 
pgbash> ??o 
exec_sql: exec_sql [option] ["SQL"]

    -i        turn on  CGI mode and set DATAs received from server
    --------------------- show infomations ---------------------
    -h item   help {\"SQL\", ALL,OPTION,ERRCODE}
    -v        show PostgreSQL and PGBASH VERSION
    -m        show CONNECTION NAMEs
    ...

pgbash> ?v 
# PostgreSQL 7.1 on i386-unknown-freebsd3.4, compiled by GCC 2.7.2.3
# Pgbash version 2.2 ( bash-2.05.0(1)-release )

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 
  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 
  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
set OPTION_NULLSTRING="-NULL-";
set OPTION_ZEROSTRING="-0-";

set CONNECTION db2;
SELECT * FROM test2 ;

set CONNECTION postgres;
begin;
declare cur cursor for select * from test;
if (( SQLCODE == SQL_OK ));
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]} .."
_list_databases
_list_tables
_list_indexes
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 ""
set EXEC_SQL_OPTION CGI;
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;
  ?m
  # 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;
  ?m
  +---+--------------+-----------+-------------------------------+
  | 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;
  ?m
  +---+--------------+-----------+-------------------------------+
  | 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"
or 
set OPTION_NULLSTRING="-NULL-";
set OPTION_ZEROSTRING="-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' "select * from test"
or
set OPTION_FRAME=TRUE;
set OPTION_CAPTION="TEST TABLE";
select * from test;

TEST TABLE
+--------+-----------+----------+
| code   | name      | address  |
+--------+-----------+----------+
| 111    | sakaida   | kobe     |
| 222    | haruhiko  | hirosima |
| 333    | nobu      |          |
|        |           | osaka    |
+--------+-----------+----------+
(4 rows)

exec_sql -H -C 'TEST TABLE' -N '-NULL-' -Z '-0-' 
                            "select * from test"
or
set OPTION_HTML=TRUE;
set OPTION_NULLSTRING="-NULL-";
set OPTION_ZEROSTRING="-0-";
select * from test;

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

(4 rows)

Output example of the database information

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> ?di 
# Database = postgres
+----------+----------+-------+
| Owner    | Relation | Type  |
+----------+----------+-------+
| postgres | id_ind   | index |
| postgres | vr_ind   | index |
+----------+----------+-------+
(2 rows)

(Shell script)
#!/usr/local/bin/pgbash
source /etc/pgbashrc
echo "Content-type: text/html"
echo ""
set EXEC_SQL_OPTION CGI;
connect to admin as db1 user admin $passwd;
set OPTION_CAPTION="MY DATABASE";
_list_databases

MY DATABASE
Database Owner Encoding
admin admin EUC_JP
pgbash pgbash SQL_ASCII
postgres postgres SQL_ASCII
template1 postgres SQL_ASCII 

(4 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 2001/05/01