- Display the retrieval results immediately
(1) Interactive environment
When SELECT is executed in the interactive environment, the retrieval results will be displayed immediately.
ex)
pgbash> connect to postgres_db user xxx NULL;
pgbash> select * from friends;
user_id | name | zip | pref_id | address
---------+----------+----------+---------+------------
22 | xxx.mmm | 611-2222 | 77 | kobe-shi
23 | vvvvvvv | 622-3333 | 75 | osaka-shi
24 | gggggg | 633-4444 | 77 | tokyo
....
(12 rows)
|
(2) Shell script
When SELECT is executed in a shell script, the retrieval results will be displayed immediately. This is the same as the interactive environment.
ex)
("go.bat" file)
#!/usr/local/bin/pgbash
connect to postgres_db user xxx NULL;
select * from from friends;
|
pgbash> chmod +x go.bat
pgbash> go.bat
- Substitute the retrieval result into the shell variable
By using 'SELECT INTO :host_variable' or 'FETCH INTO :host_variable', the retrieval result can be substituted to the shell variable.
SELECT col1, col2, ..
[INTO :host_var1 [[INDICATOR] :ind_var1], ...
:host_varN [[INDICATOR] :ind_varN]]
..
FETCH [option] [IN] cursor_name
[INTO :host_var1 [[INDICATOR] :ind_var1], ...
:host_varN [[INDICATOR] :ind_varN]];
host_var...... host variable
ind_var....... indicator variable(NULL is "1", NOT NULL is "0")
ex)
select col1, col2 into :col1, :col2 from test where code='111';
select version() into :version;
select count(*) into :count from test;
begin;
declare cur cursor for select * from test;
declares -i x ; let x=0; while ((x < $count))
do
FETCH IN CUR INTO :code INDICATOR :ind_c,
:name:ind_n, :address :ind_a;
if (( SQLCODE == SQL_OK )); then
if ((ind_n != SQL_NULL || ind_a != SQL_NULL)); then
echo "$code, $name, $address"
let x=x+1
fi
else
if(( SQLCODE == SQL_NOT_FOUND )); then
let x=11
else
echo "$SQLERRMC"
let x=x+1
fi
fi
done
end;
|
When using INTO clause, the retrieval result has to be only one row. If the retrieval results are many rows, the retrieval results are displayed to the standard output.
Here, SQLCODE means a SQL error code and $SQLERRMC means a shell variable which shows a SQL error message. In addition, SQL_NULL and SQL_NOT_FOUNF is a default value which Pgbash sets up. (see error code) It is better to use an English small letter for a host variable. Because Pgbash use English capital letter as shell variable names.
# In the (( )) operation style, it is possible to omit head '$ ' of the integer type shell variable.
Display the retrieval results in every one page
Pgbash outputs the retrieval results continuously. Therefore, if you want to stop displaying continuously, you must add the pipe '|' and 'more'.
ex)
pgbash> select * from test; | more (or less)
Write the retrieval results to the file.
It is very easy to write the retrieval results to the file by Pgbash.
"&> file name" is added behind "SQL;".
ex)
pgbash> select * from test; &> /tmp/sel.dat
|