◎ 対話型環境におけるSQLの実行例
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
◎ SQLシェル変数の使用例
SQLシェル変数は、SQL実行毎に毎回更新されますので、この値を保持したい場合は別のシェル変数に代入しておく必要があります。
- $SQLCODEシェル変数
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、${SQLFILEDNAME[i]}シェル変数
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シェル変数
insert into test values(123,'aaa','bbb');
oid = $SQLOID
select * from test where oid=$oid;