Help and database infomation
The exec_sql command has help options as follows. The help options
are judged by only 2 characters of the head, and the 2 characters have
no distinction of upper/lower character.
exec_sql -h select --- help SELECT
exec_sql -h 'create table' --- help CRAETE TABLE
exec_sql -h HELP --- help SQL commands list
exec_sql -h ALL --- help all the SQL details
exec_sql -h OPTION --- help exec_sql options
exec_sql -h ERRNO --- help SQL errno
exec_sql -h EXAMPLE --- help SQL examples
Also, the exec_sql command has the display function of the database
information as follows. It is possible to describe
'exec_sql -l database -l table -l index -p test1'.
The options such as a 'database' or 'table' are judged by only 2
characters of the head, and the 2 characters have no distinction
of upper/lower character.
exec_sql -l da --- list all the database names
exec_sql -l ta --- list only table names
exec_sql -l in --- list only index names
exec_sql -l se --- list only sequence tables
exec_sql -l gr --- list grant/revoke permissions
exec_sql -l fu --- list functions
exec_sql -l op --- list operators
exec_sql -l ty --- list types
exec_sql -l sy --- list system tables
exec_sql -p name --- list table's colomn name, index name,..etc.
exec_sql -p '*' --- list all the table's colomn name, index name,..etc.
The ~/.pgbashrc has described some shorten commands by using a exec_sql
help functions and the database information display functions.
When '?' was typed at prompt line, The shorten commands list is
displayed. If you want to know the content of shorten commands,
please refer to the ~/.pgbashrc file.
h [SQL]: help <SQL> syntax or all the 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
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;
Error code
Pgbash sets SQL error codes to next shell variables. The shell
variables are diaplayed by using 'exec_sql -h errno'
(interactive mode: 'h errno').
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) exec_sql "insert into test values(111,'aaa','bbb')"
if(( SQLCODE < SQL_OK ))
then
echo $SQLERRMC
fi
It is possible to display error codes, etc.. using the -s option.
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.