- How to describe SQL
(1) Description way
[timespec] SQL; [pipeline][redirection][&]
or
[timespec] exec_sql [option] "SQL" [pipeline][redirection][&]
You can execute SQL by only inputting SQL statement in the both of interactive environment and a shell script.
You can execute SQL with a timespec, pipekine, redirection and background_job options.
'SQL;' is changed into 'exec_sql "SQL"' at the inside of Pgbash. Therefore, 'SQL;' is equal to 'exec_sql "SQL"'.
(2) End mark of SQL (is ';')
End mark of SQL is surely required when you describe SQL direct. But, if you describe 'exec_sql "SQL"', End mark of SQL is not necessarily required.
(3) The new-line in the middle of SQL statement
You can input a new line code at the delimiter of the statement. When inputting a new line code in the interactive environment, '>' prompt is displayed. You can input SQL statement continuously at the time. When a semicolon appears, it is regarded as the end of SQL.
(Example in the interactive environment)
pgbash> insert into test values(111,
> 'aaa','bbb'
>);
(Example in a shell script)
insert into test values(111,
'aaa','bbb'
);
|
(4) Shell variables
You can use shell variables in SQL sattement. When you refer to a value, you must add the '$' mark at the head of the shell variable.
ex)
_aa='123'
_bb='name'
_cc='address'
insert into test values($_aa, '$_bb', '$_cc');
COL1='name'
VAL1='SUZUKI ICHIRO'
select $COL1, $COL2 from test where $COL1='$VAL1';
SQL="select * from test"
exec_sql "$SQL"
If a single quotation character data exists, you must surround the data by \' .
ex) DATA="name'123"
select * from test where name=\'$DATA\';
- How to execute SQL
(1) Interactive environment
When inputting SQL in the interactive environment, it is executed immediately. In the case of SELECT, the retrieval results are immediately displayed.
ex)
select * from test; | more .................. pipeline
select * from test; &> /tmp/xx.dat .......... redirection
select * from test; & ....................... background_job
time select * from test; .................... timespec
select * from test; &> /tmp/xx.dat & ........ redirection and background_job
exec_sql "select * from test" | more
exec_sql "select * from test" &> /tmp/xx.dat
(2) Shell script
It is performed as follows, in order to perform the same processing as the above-mentioned.
example) "sel.bat" shell script
#!/usr/local/bin/pgbash
connect to postgres user postgres;
select * from test;
disconnect all; ...... disconnect is not necessarily required
|
pgbash> sel.bat | more .................. pipeline
pgbash> sel.bat &> /tmp/xx.dat .......... redirection
pgbash> sel.bat & ....................... background_job
pgbash> time sel.bat .................... timespec
pgbash> sel.bat &> /tmp/xx.dat & ........ redirection and background_job
(3) Stop displaying retrieval results
When displaying the results on the screen, if you want to stop it, you must input Ctrl+C.
However, even if Ctrl+C is inputted, a screen display does not immediately stop. Dozens of lines will be displayed.
(4) Memory over
It may become 'memory over' if a lot of retrieval results are displayed. If you want to display a lot of results, you must add 'limit' option.
exj
pgbash> select * from test limit 500; | more ............. max. 500 lines
pgbash> select * from test limit 500 offset 100; | more .. max. 500 offset 100
Limitation for describing SQL
(1) Position of SQL
You must specify SQL at the head position of the line.
You can not specify SQL at the back position of 'IF/WHILE'.
| incorrect example | correct example |
if SQL * from test;
then
...
fi
|
select * from test;
if (( SQLCODE == 0 )); then
...
fi
|
If you want to know the status after executing SQL, you can refer to 'SQLCODE' shell variable.
(2) Reserved word
Pgbash parses it as SQL if the next reserved word exists at the head position of the line.
"ABORT", "ALTER", "ANALYZE",
"BEGIN",
"CHECKPOINT", "CLOSE", "CLUSTER",
"COMMENT", "COMMIT", "CONNECT",
"COPY",
"CREATE", "DECLARE", "DELETE",
"DISCONNECT", "DROP",
"END", "EXPLAIN", "FETCH",
"GRANT",
"INSERT", "LISTEN", "LOAD",
"LOCK",
"MOVE",
"NOTIFY",
"RESET", "REVOKE", "ROLLBACK",
"SELECT", "SET", "SHOW",
"TRUNCATE",
"UNLISTEN", "UPDATE",
"VACUUM",
|
Pgbash demands an input until a semicolon appears.
(3) Interruption of the SQL input
Please do as following when you want to interrupt the SQL input.
a. First line : Ctrl+C
b. Second or later line : Ctrl+D
(4) The case in which a reserved word overlaps with a command name.
When a program name overlaps with an SQL reservation word, you must specify a path of a program file.
pgbash> ./ALTER
pgbash> /home/admin/ALTER
Single/Double quotation in SQL statement
It is necessary to put two single quotations to deal with one single quotation character data in SQL statement.
ex) insert into test values(111,'aaa''aaa');
If you want to put one single quotation to deal with one single quotation character data,
you must surround the data by \' .
ex) DATA="aaa'aaa"
insert into test values(111,\'aaa'aaa\');
insert into test values(111,\'$DATA\');
You had to put \" as a double quotation until pgbash-2.4a.1, but it is not necessary to put \ in pgbash-2.4a.2.
exj select aa as "Name", bb as "Type" from test;
Comment in SQL statement
It is considered that from '#'character to line feed is a comment.
ex)
create table member (
#---------------------------------------------------------------
userid int4 # User code
primary key not null,
#---------------------------------------------------------------
kind varchar(12) # Kind of user
check(kind in ('A','B')),# ('A', or 'B')
#---------------------------------------------------------------
name varchar(32) not null, # User name
#---------------------------------------------------------------
zip char(8) # zip code
default '000-0000',
#---------------------------------------------------------------
address varchar(64) not null, # User address
#---------------------------------------------------------------
tel varchar(16) not null, # Telephone
#----------------------------------------------------------------
email varchar(64), # Mail address
#----------------------------------------------------------------
up_date timestamp # Update date
default 'now'
#----------------------------------------------------------------
);
|