◎ 対話型環境におけるSQLの実行例
prompt> /usr/local/bin/pgbash
Welcome to the PGBASH version 2.1 (bash-2.03)
Type '[time] SQL; [pipeline][redirection][&]' to execute SQL.
Type 'exec_sql [option] ["SQL"]' to execute SQL with options.
Type '?' for HELP. (This help is defined with ~/.pgbashrc)
pgbash> ?
? : this help
h [SQL]: help <SQL> syntax or all SQL_commands
o : help OPTIONs of 'exec_sql' function
v : print PGBASH VERSION
s : print STATUS after SQL execution
u : list all USERs
l : list all DATABASEs
m : list all CONNECTIONs
d [TBL]: list tables,indices,columns in <TaBLe>, or all tables
dt : list only TABLEs
di : list only INDEXes
ds : list only 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
pgbash> h
# HELP: Command Name List:
abort transaction alter table alter user
begin work cluster close
commit work connect disconnect
以下省略
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] )
[INHERITS (class_name1, ...class_nameN)
;
pgbash> h errno
# 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.
SQL_TOO_FEW_ARGUMENTS : -202 : too few arguments in fetch_stmt.
SQL_CONNECT_ERROR : -203 : database connection error.
以下省略
pgbash> o
# Option:
-i set CGI mode and set DATA received from WWW server
-h item help item: select,insert,.. or next command:
[HElp,ALl,OPtion,ERrno,EXample]<2 char are judged>
-v display VERSION of pgbash(exec_sql)
-s display SQLCA(SQLCODE,etc..) (after SQL execution)
以下省略
pgbash> v
# PostgreSQL 7.0.0 on i386-unknown-freebsd2.2.7, compiled by gcc 2.7.2.1
# PGBASH version 2.1 (bash-2.03)
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 (shell variable)
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 (shell variable)
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シェル変数
#!/usr/local/bin/pgbash
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]}シェル変数
#!/usr/local/bin/pgbash
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;