- How to display the help menu.
The next help menu will be displayed if '?' is inputted in the interactive environment.
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
...
|
- Reserved words of SQL.
The next reserved words will be displayed if '??h' is inputted in the interactive environment.
pgbash> ??h
# HELP: Command Name List:
ABORT ALTER GROUP ALTER TABLE
ALTER USER ANALYZE BEGIN
CHECKPOINT CLOSE CLUSTER
....
|
- Syntax of SQL
If you want to know the syntax of SQL, please input '??h RESERVED_WORD'. For example, '??h "create table"'.
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 [, ... ] ) ]
...
|
- How to display the database information in the interactive environment
If you want to know the database information, please input the next command.
?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
_lo_list : list large_object
(Example)
pgbash> ?u
[ List of user names ]
Username | SupperUser | CreateDB
----------+------------+----------
postgres | yes | yes
admin | no | yes
pgbash | no | yes
(3 rows)
|
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)
|
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)
|
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
|
How to display the database information in the shell script
You can use the functions defined by 'pgbashrc'.
(pgbashrc)
| 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_list | _lo_list | list large_object |
(Example)
#!/usr/local/bin/pgbash
source /etc/pgbashrc
# ------- Connect to the database --------
CONNECT TO postgres;
_show_connection .......... Status in connecting the database
# ------ Rows and columns of retrieval results -------
SELECT * FROM test2 ;
echo "nFields=$SQLNFIELD FieldName=${SQLFIELDNAME[0]} .."
# ------ Database information --------
_list_databases ............ Database list
_list_tables ............... Table list
_list_indexes ............... Index list
|
|