- Help menu
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
?dp : list PERMISSIONs
?ds : list SEQUENCEs
?m : show CONNECTION NAMEs
?o : show current values of exec_sql_OPTIONs
?s : show STATUS after executing SQL
?v : show PostgreSQL and pgbash VERSION
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('')
O+ [str] : set TABLE TAG (e.g. 'cellpadding=4') or DEFAULT('')
_lo_import : lo_import <'COMMENT'>
_lo_export : lo_export
_lo_unlink : lo_unlink
_lo_list : list LARGE_OBJECTs (?dl)
? : 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
?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
|
- How to use Pgbash
pgbash> ??
# Usage:
1. Type 'connect to DB [as CONNECT_NAME] [user UID [PASSWD|NULL]];' , or
'connect to DEFAULT' is automatically issued before executing SQL.
ex)
connect to testdb as db1 user admin xxxx;
connect to testdb as db2 user admin NULL; <-- password is unnecessary
connect to testdb as db3 user admin; <-- password prompt is displayed
connect to testdb2; <--- CONNECT_NAME becomes 'testdb'
connect to DEFAULT; <--- DB/CONNECT_NAME/UID is login-userid
connect to db1@db.xxx.co.jp; <-- DB server name is 'db.xxx.co.jp'.
2. Type 'set connection CONNECT_NAME;' to change the connection. , or
CURRENT connection is used.
ex)
connect to db1 user admin xxxxx;
connect to db2;
set connection db1;
select * from test;
3. Type '[timespec] SQL; [pipeline][redirection][&]' to execute SQL.
ex)
select * from test limit 300;
time select * from test; &> /tmp/test.dat
select * from test; | more
select * from test; &> /tmp/test.dat &
4. Type 'disconnect [CONNECT_NAME | ALL];'
(Connections are automatically closed when Pgbash is terminated.)
ex)
disconnect db1;
disconnect all;
|
- SQL syntax
(1) List of SQL reserved words
pgbash> ??h
# HELP: Command Name List:
ABORT ALTER GROUP ALTER TABLE
ALTER USER ANALYZE BEGIN
CHECKPOINT CLOSE CLUSTER
COMMENT COMMIT CONNECT
COPY COPY TABLE() CREATE AGGREGATE
CREATE CONSTRAINT TRIGGER CREATE DATABASE CREATE FUNCTION
CREATE GROUP CREATE INDEX CREATE LANGUAGE
CREATE OPERATOR CREATE RULE CREATE SEQUENCE
CREATE TABLE CREATE TABLE AS CREATE TRIGGER
CREATE TYPE CREATE USER CREATE VIEW
DECLARE DELETE DISCONNECT
DROP AGGREGATE DROP DATABASE DROP FUNCTION
DROP GROUP DROP INDEX DROP LANGUAGE
DROP OPERATOR DROP RULE DROP SEQUENCE
DROP TABLE DROP TRIGGER DROP TYPE
DROP USER DROP VIEW END
EXPLAIN FETCH FETCH INTO
GRANT INSERT LISTEN
LOAD LOCK MOVE
NOTIFY REINDEX RESET
REVOKE ROLLBACK SELECT
SELECT INTO SET SET EXEC_SQL_OPTION
SET CONNECTION SET CONSTRAINTS SET SESSION AUTHORIZATION
SET TRANSACTION SHOW TRUNCATE
UNLISTEN UPDATE VACUUM
|
(2) SQL 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 [, ... ] ) ]
...
|
- Error code
pgbash> ??e
# SQLCODE
ex)
begin;
declare cur cursor for select code,name from test order by code;
while (( SQLCODE == SQL_OK )); do
fetch in cur into :code, :name;
if (( SQLCODE < 0 || SQLCODE == SQL_NOT_FOUND)); then
break
else
echo "code=$code name=$name"
fi
done
end;
+------------------------+-------------------------------------------+--------
| Value Name | Comment | Value
+------------------------+-------------------------------------------+--------
| SQL_OK | normal end. |0
| SQL_NOT_FOUND | EOF(End Of File). |100
| SQL_SYSTEM_ERROR | system error. |-200
| SQL_TOO_MANY_ARGUMENTS | too many arguments in fetch_stmt. |-201
| SQL_TOO_FEW_ARGUMENTS | too few arguments in fetch_stmt. |-202
| SQL_CONNECT_ERROR | database connection error. |-203
| SQL_INVALID_STMT | invalid statements. |-230
| SQL_READONLY_SHELLVAR | can not set read-only shell variable. |-231
| SQL_DB_NOT_OPEN | database not open. |-232
| SQL_CNAME_NOT_FOUND | connect-name not found. |-233
| SQL_CNAME_ALREADY_USE | connect-name already exist. |-234
| SQL_INVALID_COMMAND | invalid command. |-235
| SQL_INVALID_DATA | invalid data. |-236
| SQL_BAD_RESPONSE | bad response(backend maybe died). |-400
| SQL_EMPTY_QUERY | empty query (backend lost query). |-401
| SQL_CONNECTION_BAD | connection bad(disconnect backend) |-403
| SQL_FATAL_ERROR | query fatal error (SQL error on backend)|-403
| SQL_NONFATAL_ERROR | query nonfatal error(SQL error on backend)|-404
| SQL_NULL | indicator is NULL. |-1
+------------------------+-------------------------------------------+--------
|
- List of functions in Pgbashrc
pgbash> ??f
# 'pgbashrc' functions for using a shell script
ex)
#!/usr/local/bin/pgbash
source ~/.pgbashrc
_list_relations
_list_relation table_name
+-------------------+-----------+---------------------------------------
| Function name |Interactive| Comment
+-------------------+-----------+---------------------------------------
| _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
| _lo_list |_lo_list | list large_objects
+-------------------+-----------+---------------------------------------
|
- exec_sq options
pgbash> ??o
exec_sql: exec_sql [option] ["SQL"]
--------------------- 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
-H turn on HTML format(-H is default in CGI mode)
-X turn on EXPANDED format
-L turn on OUTER_FRAME of PLAIN text
-A turn off ALIGNMENT of PLAIN text
-T turn off TOP_header print
-B turn off BOTTOM_count print
-C cap set CAPTION of HTML/PLAIN table(e.g. -C 'TITLE')
-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)
|
- Status of connecting databases
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)
|
Value of options
pgbash> ?o
# EXEC_SQL_OPTION
Usage:
set EXEC_SQL_OPTION DEFAULT ; --- reset DEFAULT
set EXEC_SQL_OPTION CGI ; --- set CGI mode
set <option_name>[=value] ;
ex)
SET OPTION_ECHO; or SET OPTION_ECHO=ON; or SET OPTION_ECHO=TRUE;
SET OPTION_BOTTOM=OFF; or SET OPTION_BOTTOM=FALSE;
set OPTION_SEPARATOR=',';
set OPTION_NULLSTRING="\N";
+-------------------+-------------------------+-------+---------------
| <option_name> | Comment |DEFAULT| Current Value
+-------------------+-------------------------+-------+---------------
| OPTION_ECHO | turn on/off ECHO query | OFF | OFF
| OPTION_QUIET | turn on/off QUIET mode | OFF | OFF
| OPTION_HEADER | turn on/off TOP_header | ON | ON
| OPTION_BOTTOM | turn on/off BOTTOM_print| ON | ON
| OPTION_ALIGNMENT | turn on/off ALIGNMENT | ON | ON
| OPTION_FRAME | turn on/off OUTER_FRAME | OFF | OFF
| OPTION_EXPANDED | turn on/off EXPANDED | OFF | OFF
| OPTION_SEPARATOR | set SEPARATOR character | '' | ''
| OPTION_NULLSTRING | set NULL string | "" | ""
| OPTION_ZEROSTRING | set ZERO string | "" | ""
| OPTION_CAPTION | set CAPTION | "" | ""
+-------------------+-------------------------+-------+---------------
[ CGI_mode / HTML_output_mode ]
+-------------------+-------------------------+-------+---------------
| <option_name> | Comment |DEFAULT| Current Value
+-------------------+-------------------------+-------+---------------
| OPTION_HTML | turn on/off HTML mode | OFF | OFF
| OPTION_TABLETAG | set HTML table tag | "" | ""
| OPTION_HEADERTR | set HTML header <TR> | "" | ""
| OPTION_HEADERTH | set HTML header <TH> | "" | ""
| OPTION_BODYTAG | set HTML body <TD> tag | "" | ""
| OPTION_INPUTTAG | turn on/off INPUT tag | OFF | OFF
| OPTION_INPUTSIZE | set HTML input tag SIZE | "" | ""
+-------------------+-------------------------+-------+---------------
|
Status after executing SQL
(1) The case of normal end
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 =
|
(2) The case of abnormal end
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
|
Version number
pgbash> ?v
# PostgreSQL 7.x on i386-unknown-freebsd3.4, compiled by GCC 2.7.2.3
# Pgbash version 2.x ( bash-2.05.0(1)-release )
|
List of database users
pgbash> ?u
[ List of user names ]
Username | SupperUser | CreateDB
----------+------------+----------
postgres | yes | yes
admin | no | yes
pgbash | no | yes
(3 rows)
|
List of databases
pgbash> ?l
[ List of databases ]
Name | Owner | Encoding
-----------+----------+-----------
admin | postgres | EUC_JP
postgres | postgres | EUC_JP
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
test | postgres | SQL_ASCII
(5 rows)
|
List of relations
(1) List of relation names.
pgbash> ?d
[ List of relations ]
Name | Type | Owner
-----------------+----------+----------
member | table | postgres
member_log | table | postgres
member_log_view | view | postgres
member_passwd | table | postgres
member_seq | sequence | postgres
test | table | postgres
(6 rows)
|
(2) List of attributes of relation
pgbash> ?d member
[ "member" data definitions ]
Attribute | Type | NotNull | Default
-----------+-----------------------------+----------+------------
userid | integer | not null |
kind | character varying(12) | | '*'
staff | character varying(24) | | ''
name | character varying(32) | not null |
zip | character(8) | | '000-0000'
address1 | character varying(64) | not null |
address2 | character varying(64) | |
tel | character varying(16) | not null |
email | character varying(64) | |
org | character varying(128) | |
new_date | date | | 'now'
up_date | timestamp(6) with time zone | | 'now'
(12 rows)
PrimaryKey: member_pkey
Unique Key: member_tel
Index Key: member_email
Index Key: member_name
Attribute : member_kind
Constrain : (((kind = '*'::"varchar") OR (kind = 'net'::"varchar")) OR
(kind = 'post'::"varchar"))
Attribute : member_zip
Constrain : ((zip >= '000-0000'::bpchar) AND (zip <= '999-9999'::bpchar))
Rule : member_rule
Trigger : RI_ConstraintTrigger_118843
Trigger : RI_ConstraintTrigger_118845
|
List of tables
pgbash> ?dt
[ List of tables ]
Name | Type | Owner
--------------------+-------+-------
member | table | admin
member_log | table | admin
member_passwd | table | admin
pgbash_description | table | admin
test_vacuum | table | admin
(5 rows)
|
List of indexes
pgbash> ?di
[ List of indexes ]
Name | Type | Owner
--------------------------+-------+-------
bid_index | index | admin
member_email | index | admin
member_name | index | admin
member_passwd_pkey | index | admin
member_pkey | index | admin
member_tel | index | admin
pgbash_description_index | index | admin
(7 rows)
|
List of views
pgbash> ?dv
[ List of views ]
Name | Type | Owner
-----------------+------+-------
member_log_view | view | admin
(1 row)
|
List of large objects
pgbash> ?dl (or _lo_list)
[ List of large objects ]
OID | Description
--------+--------------
118865 | member data1
118867 | member data2
(2 rows)
|
List of permission
pgbash> ?dp
[ List of access permissions ]
Relation | Access privileges
--------------------+------------------------------------
member | {"=","postgres=r","admin=arwdRxt"}
member_log |
member_log_view | {"=","postgres=r","admin=arwdRxt"}
member_passwd | {"=","postgres=r","admin=arwdRxt"}
member_seq |
pgbash_description |
test_vacuum |
(7 rows)
|
List of sequnces
pgbash> ?ds
[ List of sequences ]
Name | Type | Owner
------------+----------+-------
member_seq | sequence | admin
(1 row)
|
|