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

Usage of Pgbash

Pgbash executes 'SQL;' as a shell command. By using the exec_sql command, it is also possible to execute SQL with various options.
Start/End method of Pgbash
  1. Sub shell
    The Sub shell is the shell started from a log-in shell. If you log in, and type '/usr/local/bin/pgbash', then Pgbash is started as a sub shell by reading ~/.bashrc and ~/.pgbashrc, and Pgbash displays the following Welcome messages. 
      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> exit
    Type 'exit' ( or 'Ctrl+D'), then Pgbash is terminated and you return to a log-in shell.

    If you changed ~/.bashrc or ~/.pgbashrc while Pgbash is being used, you must update a environments using the 'source' command. For example, type 'source ~/.pgbashrc'.

  2. Shell script
    "#!/usr/local/bin/pgbash" must be described at the head of the shell script.
    #!/usr/local/bin/pgbash
    source ~/.pgbashrc ..... optional
    #
    If you want to use environments of ~/.pgbashrc, you must describe the 'source' command.

    (Attention)
    The newline code of a shell script is 'LF'. If 'CR+LF', then a shell script can not be executed.
 Description method of the SQL statement
    You can execute SQL only typing 'SQL;'. It is possible to execute SQL with TIMESPEC, PIPELINE, REDIRECTION and BACKGROUND_JOB. The description method is next.

    [timespec] SQL; [pipeline][redirection][&]

    It is possible to freely write the SQL statement in the multiple line. The '>' prompt is displayed when line was feeded. SQL statement can be inputted continuously until the semicolon appears.

    example)
    pgbash> select * from test;
    pgbash> time select * from test; | more > /tmp/sel.dat &
    pgbash> insert into test values(111,
    > 'aaa','bbb');

    'SQL;' can not be written after a 'if/while,..etc.' control word. In this case, the part of 'SQL;' must be written like 'exec_sql "SQL"'.

    It is possible to use shell variables in the 'SQL;'. However, in the FETCH INTO statement, you must describe ':' and a shell variable as a host variable.

    example)
    insert into test values($_AA, '$_BB', '$_CC');
    fetch in cur into :_AA, :_BB, :_CC;
The exec_sql execution method and temporary options
    If you want to execute SQL with various temporary options, you must describe the exec_sql command. The exec_sql command format is next.

    exec_sql [option] ["SQL"]
  1. Description method
    The SQL statement must be described by surrounding double quart. The ';' may not be added at the end of SQL. It is possible to freely write the SQL statement in the multiple line. 
      example) exec_sql "select aa,bb,cc from test_table
                          where a>'123' and dd<'999'"
  2. exec_sql command options
       -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
   -s        show STATUS after executing SQL
   --------------------- temporary options --------------------
   -d conn   set  CONNECTION NAME
   -e        turn on  ECHO  query sent to the backend
   -q        turn on  QUIET system/sql error messages
   -P        turn on  PLAIN format(-P is default in normal mode)
   -H        turn on  HTML  format(-H is default in CGI    mode)
   -X        turn on  EXPANDED  format
   -L        turn on  OUTER_FRAME  of PLAIN text
   -T        turn off TOP_header   print
   -B        turn off BOTTOM_count print
   -A        turn off ALIGNMENT    of PLAIN text(e.g. -PXA) 
   -C cap    set CAPTION of HTML/PLAIN table(e.g. -C 'TITLE')
   -O opt    set OPTION  of HTML table tag(e.g. -O 'BORDER=0')
   -S sep    set SEPARATOR of PLAIN text field (default is '|')
   -N null   set NULL_STRING(e.g. -N '*N*')
   -Z zero   set ZERO_STRINg(e.g. -Z '*0*') 
   --------------------- execute function  --------------------
   -x func   execute func(e.g. lo_import, lo_export, lo_unlink)

Options
    The 'exec_sql' options is temporary, but you can set the effective option for all SQL statements by using 'set' statement.

    set EXEC_SQL_OPTION DEFAULT;
    set EXEC_SQL_OPTION CGI;
    set option_name[=value];
    (1) DEFAULT
    'set EXEC_SQL_OPTION DEFAULT;' sets all the option to the default state.

    (2) CGI
    'set EXEC_SQL_OPTION CGI;' sets a shell script to CGI mode.

    (3) The other options

    example)
    set OPTION_ECHO=TRUE; または set OPTION_ECHO=FALSE;
    set OPTION_SEPARATOR=',';
    set OPTION_NULLSTRING="*N*";

    option_nameCommentDEFAULT
    value
    OPTION_ECHOturn on/off ECHO query
    sent to the backend    		FALSE
    OPTION_QUIETturn on/off QUIET system/sql
    error messages     		FALSE
    OPTION_HEADERturn on/off TOP_header print
    		TRUE
    OPTION_BOTTOMturn on/off BOTTOM_count print
    		TRUE
    OPTION_ALIGNMENTturn on/off ALIGNMENT
    of PLAIN text    		TRUE
    OPTION_FRAMEturn on/off OUTER_FRAME
    of PLAIN text    		FALSE
    OPTION_EXPANDEDturn on/off EXPANDED format FALSE
    OPTION_HTMLturn on/off HTML mode FALSE
    OPTION_SEPARATORset SEPARATOR of PLAIN text '|'
    OPTION_NULLSTRINGset NULL string ""
    OPTION_ZEROSTRINGset ZERO string ""
    OPTION_CAPTIONset CAPTION string ""
    OPTION_TABLETAGset OPTION of HTML table tag ""

 Help and database infomation
  1. Help Menu
    pgbash> ? 
       ??        : 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
   ...

  2. SQL reserved words
    pgbash> ??h 
       # HELP: Command Name List:
   ABORT                    ALTER GROUP              ALTER TABLE
   ALTER USER               BEGIN                    CHECKPOINT
   CLOSE                    CLUSTER                  CONNECT
   ...

  3. SQL command syntax
    pgbash> ??h "crete table" 
      # SQL command: CREATE TABLE
  Description: Creates a new table
  Syntax :
  CREATE [ TEMPORARY | TEMP ] TABLE table_name (
     { column_name type [ column_constraint [ ... ] ]
     | table_constraint }  [, ... ]
     ) [ INHERITS ( inherited_table [, ... ] ) ]
     ...

  4. Database Information
    pgbash> ? 
       ...
   ?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
   ...

    If you use a shell script, then you must use the functions defined in the 'pgbashrc'.
    example)
    #!/usr/local/bin/pgbash
    source ~/.pgbashrc
    _list_relations
    _list_relation table_name

    Function nameInteractiveComment
    _list_users ?u list database USERs
    _list_databases ?l list DATABASEs
    _list_relations ?d list RELATIONs
    _list_relation ?d [rel] list RELATION for tbl/view/idx/seq
    _list_tables ?dt list TABLEs
    _list_indexes ?di list INDEXes
    _list_views ?dv list VIEWs
    _list_sequences ?ds list SEQUENCEs
    _list_permissions ?dp list PERMISSIONs
    _list_lobjects ?dl list LARGE_OBJECTs
    _list_aggregates ?dA list AGGREGATEs
    _list_descriptions ?dD list DESCRIPTIONs
    _list_description ?dD [obj] list DESCRIPTION for tbl/typ/func/ope
    _list_functions ?dF list FUNCTIONs
    _list_operators ?dO list OPERATORs
    _list_systables ?dS list SYSTEM_TABLEs
    _list_types ?dT list data_TYPEs
    _lo_import _lo_import large_object_import
    _lo_export _lo_export large_object_export
    _lo_unlink _lo_unlink large_object_unlink

Connect to database

    Pgbash has implemented CONNECT, DISCONNECT and SET CONNECTION statements. SET CONNECTION changes the current database connection name. If SET CONNECTION is not defined, then the last CONNECT will be the current connection name. And DISCONNECT disconnects database. Each syntax are next. 
      ・CONNECT TO dbname[@server[:port]] | DEFAULT  
                     [AS connect_name] [USER user_name [password]];
  ・DISCONNECT connect_name|CURRENT|DEFAULT|ALL;
  ・SET CONNECTION connect_name|DEFAULT;

 ex.) connect to postgres1 as db1 user postgres xxxxxxxx;
      connect to postgres2 as db2 ;   ....login user name is used.
      set connection db1;
      select * from test1;
      set connection db2;
      select * from test2;
      disconenct all;
    If you execute SELECT without CONNECT statement, CONNECT TO DEFAULT is automatically issued. This is the same thing when you execute psql without database name.

    If you describe USER and not describe PASSWORD, password: prompt is displayed, and it becomes a condition waiting for password-input. If the password is not defined, you describe NULL as a password like "CONNECT TO dbname USER username NULL". In the case that the login username is equal to the database user name, it is not necessary to describe USER clause.

    It is possible to disconnect all the databases using "DISCOONECT ALL". Still, when Login shell(or Sub shell) finish, all the connections are automatically disconnected.

Display the connection status of the database

    The connection status of the database is displayed, when the '?m' is executed. 
        connect to default;
    connect to postgres@www2.psn.ne.jp:5432  as db1;
    connect to admin@xxx.psn.ne.jp as db2 user admin;
    exec_sql -mL ( interactive mode: m )
    # Connected Databases List (C: current database is '*')
    +---+--------------+-----------+------------------------------+
    | C | connect_name | user_name | target_name                  |
    +---+--------------+-----------+------------------------------+
    |   | _DEFAULT_    | postgres  | postgres:5432                |
    |   | db1          | postgres  | postgres@www.psn.ne.jp:5432  |
    | * | db2          | admin     | admin@xxx.psn.ne.jp:5432     |
    +---+--------------+-----------+------------------------------+
    (3 rows)
Data manipulator of the multiple databases

    It is possible to access some databases by using "SET CONNECTION"/ "-d Option". The example is as follows. 
     ex.) connect to postgres1 as db1;
      connect to postgres2 as db2;
      connect to postgres1 as db3;
      set connection db1;    <-------------- current DB is db1
      exec_sql -d db2 "select * from test2" <-- db2 connection
      select * from test1                   <-- db1 connection
      exec_sql -d db3 "select * from test2" <-- db3 connection
      select * from test;                   <-- db1 connection
    SET CONNECTION changes the current connection name. SQL is executed for the current connection, if the -d option is not designated in the exec_sql. The -d option change the connection name temporary, but it doesn't change the current connection name continual.

    By using CONNECT statement, it is possible to connect in multiple connection names for one database. It is possible to easily make the testing environment which the multiuser is connecting with one database.

Substitution of the retrieval result to the shell variable

    Pgbash can substitute the retrieval result for the shell variable using FETCH INTO statement. Syntax of ETCH INTO statement is as follows. 
    FETCH [FORWARD|BACKWARD|RELATIVE] [number|ALL|NEXT|PRIOR] [IN|FROM] cursorname
      [INTO :host_var1 [[INDICATOR] :ind_var1], ...
            :host_varN [[INDICATOR] :ind_varN]];
    It is not possible to describe number/ALL, when INTO clause is used. However, the case in which the retrieval result is one tuple, INTO clause becomes effective, even if number/ALL is designated.

    (ex.)
    begin; 
declare cur cursor for select * from test;
declares -i x ; let x=0;  while ((x < 10))  
do
   FETCH IN CUR INTO :code INDICATOR :ind_c,
   			:name:ind_n, :address :ind_a;
    if (( SQLCODE == SQL_OK )); then
        if ((ind_n != SQL_NULL || ind_a != SQL_NULL)); then
            echo "$code, $name, $address"
            let x=x+1
        fi
    else
        if(( SQLCODE == SQL_NOT_FOUND )); then
                let x=11
        else
                echo "$SQLERRMC"
                let x=x+1
        fi
    fi
done
end;

    Here, "SQLCODE" is the SQL error code, "$SQLERRMC" is SQL error message. And, SQL_NULL/SQL_NOT_FOUND is the value which pgbash has set early. (Refer to SQL error code.) However, you may use a lower-case chracter for shell variables, because Pgbash use a upper-case character.

    # In the (( ))operation style, it is possible to omit head '$ ' of the integer type shell variable

Output form

  1. Output of standard mode
    It is possible to output the database information and the retrieval result in the same format. Generally, the plain output is a standard state, but if "set EXEC_SQL_OPTION CGI;" is executed, HTML output becomes standard state.

  2. Output of a title

    exec_sql -C 'TITLE' "select * from test;"
    or
    set OPTION_CAPTION='TITLE';
    select * from test;

  3. Output the outer frame in a plain text output

    exec_sql -L "select * from test;"
    or
    set OPTION_FRAME=TRUE;
    select * from test;
    +------+------------+----------+
| code | name       | address  |
+------+------------+----------+
| 111  | sakaida    | kobe     |
| 222  | haruhiko   | hirosima |
| 333  | nobu       |          |
|      | youko      | oosaka   |
+------+------------+----------+
(4 rows)
  4. Stop the display of header and tail(row_count)

    exec_sql -TB "select * from test"
    or
    set OPTION_HEADER=FALSE;
    set OPTION_BOTTOM=FALSE;
    select * from test;
    +------+------------+----------+
| 111  | sakaida    | kobe     |
| 222  | haruhiko   | hirosima |
| 333  | nobu       |          |
|      | youko      | oosaka   |
+------+------------+----------+
  5. Display a NULL value and bit-zero value

    exec_sql -N '*N*' -Z '-0-' "select * from test"
    or
    set OPTION_NULLSTRING="*N*";
    set OPTION_ZEROSTRING="-0-";
    select * from test; 
    +------+------------+----------+
| 111  | sakaida    | kobe     |
| 222  | haruhiko   | hirosima |
| 333  | nobu       | -0-      |
| NULL | youko      | oosaka   |
+------+------------+----------+
COPY statement

    In PostgreSQL default functionality, only postgres super user can copy between a table and a diskfile. However, in Pgbash, not only postgres super user but also a general user can copy it . 
    COPY table( [ col1[,col2..] ] ) {TO|FROM} {'fileame'|STDIN|STDOUT} 
     [USING DELIMITERS 'delim'] [WITH NULL AS 'nullstring' ];

    (1) COPY TO database from file

    If you execute "copy tbname(col1,col2) from '/tmp/oo'", the COPY command is changed as follows. 
      begin;
  insert into tbname(col1,col2) values(file's data1);
  insert into tbname(col1,col2) values(file's data2); 
  ...
  end;
   If an error was occurred, then a database is rollbacked.
    (2) COPY from database TO file

    If you execute "copy tbname(col1,col2) to '/tmp/oo'", the COPY command is changed as follows. 
      begin;
  declare copy_cur00 cursor for select col1,col2 from tbname;
  fetch 300 in copy_cur00; >> /tmp/oo
  fetch 300 in copy_cur00; >> /tmp/oo
  end;

Setting of CGI mode and reading of the data

    If "exec_sql -i" is executed, then HTML_OUTPUT<TABLE BORDER=1> becomes a default. And some datas are read by a GET/POST method and by HTTP_COOKIE from WWW server.

  1. Description example of the CGI script
    For example, here is a HomePage.
    <HTML>
    <FORM METHOD=GET ACTION="pgbash-test.sh">
    <INPUT TYPE=password NAME=passwd >
    <INPUT TYPE=text NAME="MYNAME" VALUE="sakaida">
    <INPUT TYPE=text NAME="ADDRESS" VALUE="Osaka">
    <INPUT TYPE=submit VALUE=submit>
    </HTML>

    And here is a "pgbash-test.sh" CGI script.
    #!/usr/local/bin/pgbash
    exec 2>&1 ............ (1)
    echo "Content-type: text/html" ........................ (2)
    echo ""
    set EXEC_SQL_OPTION CGI; ......(3)
    #
    connect to dbname as db1 user nopbody $passwd;
    insert into test values( '$MYNAME','$ADDRESS');
    select * from test order by name limit 100;

    (1) "exec 2>&1" is a method for making a standard error output to be a standard output. By the way, it is possible to display outputting error message in a home page. This is an optinal command.
    (2) echo "Content-type: text/html" and echo "" are indispensable
    (3) It is necessary to execute 'set EXEC_SQL_OPTION CGI;'. The shell script is changed CGI mode, and the data from the home page is set as a shell variable.

  2. Reading of the data by a GET/POST method
    It is possible to handle a data name ( in the above: passwd, MYNAME, ADDRESS) as a shell variable, when "exec_sql -i" was executed.

  3. Reading of the HTTP_COOKIE data
    When the HTTP_COOKIE has been set, By decomposing the value, it is substituted for next shell variable. 
      $HTTP_NCOOKIE        : Number of the HTTP_COOKIE.
  ${HTTP_COOKIEKEY[i]} : HTTP_COOKIE key name
  ${HTTP_COOKIEVAL[i]} : HTTP_COOKIE value   
  (i = 0 to HTTP_NCOOKIE-1)

Shell variables of Pgbash
  1. SQL condition
    Pgbash sets a SQL result condition to the next shell variables. 
      $SQLOID     :(int type) OID of recent insert
  $SQLCODE    :(int type) SQL error code (9.refer to error code)
                      0    --- normal end 
                      100  --- EOF(Eond Of File)  
                      minus--- SQL error
  $SQLERRMC   :(char type) SQL error message(max 70)
  $SQLERRML   :(int type) length of SQL error message(<70)
  $SQLERRD2   :(int type) number of tuples(PQntuples())
  $SQLERRD3   :(int type) number of fields(PQnfields())
  $SQLNTUPLE  :(int type) equal to SQLERRD2.
  $SQLNFIELD  :(int type) equal to SQLERRD3

  ${SQLFIELDNAME[i]} : filed name list(i = 0 to SQLNFIELD-1 )

  2. Option shell variables
    By using 'set option_name[=value]; ', the option value is saved as a shell variable. 
      $OPTION_ECHO       : turn on/off ECHO query
  $OPTION_QUIET      : turn on/off QUIET mode
  $OPTION_HEADER     : turn on/off TOP_header
  $OPTION_BOTTOM     : turn on/off BOTTOM_print
  $OPTION_ALIGNMENT  : turn on/off ALIGNMENT
  $OPTION_FRAME      : turn on/off OUTER_FRAME
  $OPTION_EXPANDED   : turn on/off EXPANDED
  $OPTION_HTML       : turn on/off HTML mode
  $OPTION_SEPARATOR  : set SEPARATOR character
  $OPTION_NULLSTRING : set NULL string
  $OPTION_ZEROSTRING : set NULL string
  $OPTION_CAPTION    : set NULL string
  $OPTION_TABLETAG   : set NULL string

  3. Error code
    Pgbash sets SQL error codes to the next shell variables. 
      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. 
  SQL_INVALID_STMT       : -230 : invalid statements. 
  SQL_READONLY_SHELLVAR  : -231 : can not set read-only shell variable. 
  SQL_DB_NOT_OPEN        : -232 : DB not open. 
  SQL_CNAME_NOT_FOUND    : -233 : connect-name not found. 
  SQL_CNAME_ALREADY_USE  : -234 : connect-name already exist. 
  SQL_INVALID_COMMAND    : -235 : invalid command. 
  SQL_BAD_RESPONSE       : -400 : bad response(backend maybe died)." 
  SQL_EMPTY_QUERY        : -401 : empty query (backend lost query). 
  SQL_CONNECTION_BAD     : -402 : connection bad(disconnect backend)" 
  SQL_FATAL_ERROR        : -403 : query fatal error   (SQL error on backend) 
  SQL_NONFATAL_ERROR     : -404 : query nonfatal error(SQL error on backend) 
  SQL_NULL               :   -1 : indicator is NULL.
    The error code can be used using the (( )) operation style in the shell script as follows. 
      ex)  insert into test values(111,'aaa','bbb');
       if(( SQLCODE < SQL_OK ))
       then
          echo $SQLERRMC
       fi
    Error code can be displayed by typing '?s'. 
     ex) # SQL status (shell variable)                           
       SQLCODE   = -403   (SQL error code)
       SQLNTUPLES= 0      (number of tuples)
       SQLNFIELDS= 0      (number of fields)
       SQLERRML  = 38     (length of SQLERRMC)     
       SQLERRMC  = ERROR:  testxxx: Table does not exist.
  4. The other shell variables

    (1) $SQLNFILED、${SQLFILEDNAME[i]} shell variables
    SQLNFIELD is number of column and SQLFILEDNAME[] are column names after executing a SELECT command.

    (2) $SQLOID shell variable
    SQLOIG is OID when INSERT is executed.

    (3) $POSTGRESQL_VERSION shell variable
    POSTGRESQL_VERSION is a part of PostgreSQL version number(e.g. 6.5.3, 7.0.3 or 7.1). 
     ex) VER1=${POSTGRESQL_VERSION:0:1}
     VER2=${POSTGRESQL_VERSION:2:1}
     if (( VER1 == 7 && VER2 == 1 )); then
        echo "PostgreSQL version 7.1."
     fi
Restriction

  • Bash commands name
    The "declare/set/select" bash commands are equal to SQL words. In pgbash, since SQL statement is prior handled, the bash commands name have been changed as follows. 
      bash's declare command ==> declares
  bash's set command     ==> sets
  bash's select command  ==> selects
  • Position of 'SQL;'
    'SQL;' must be located in the head of statement or 'SQL;' must be located in the back side of TIME spec. 'SQL;' must not be located in the back side of IF/WHILE stetment.

  • Shell variables after SQL execution
    If SQL is executed using PIPELINE/BACKGROUND_JOB, then a SQLCODE shell variable can not be used, because a PIPELINE/BACKGROUND_JOB is executed as the different process from bash process. Next example is a mistake.

    (1) SQL condition after using PIPELINE

    prompt> select * from test; | more
    prompt> s ...SQLCODE is always zero

    (2) fetch into on BACKGROUND_JOB

    fetch in cur into :aa, :bb; & ... can not use $aa,$bb

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

Last Modified at 2001/05/01