- Connect to the database
When connecting to the database, CONNECT statement can be used. When executing SQL without connecting to the database, "CONNECT TO DEFAULT" is automatically issued.
'DEFAULT' uses a "Unix login name" as a database name and database user name.
CONNECT TO dbname[@server[:port]] | DEFAULT [AS connect_name]
[USER user_name [[{IDENTIFIED BY | USING | /}] password]];
dbname..........database name
server..........server name
port............PostgreSQL port number(default value is 5432)
connect_name....connection name (default value is equal to dbname)
user_name.......database user name（default value is equal to a Unix login name）
passwd..........password
ex)
connect to postgres@db1.jp as db1 user postgres xxxxxxxx;
connect to postgres@db2.jp as db2 user postgres IDENITIFIED BY xxxxxxxx;
connect to postgres@db3.jp as db3 user postgres USING xxxxxxxx;
connect to postgres@db4.jp as db4 user postgres/xxxxxxxx;
connect to postgres as db4 user postgres; .....password prompt is displayed.
connect to postgres2 as db2 ; .................Unix login name is used.
connect to postgres3 user admin; ..............connection name is 'postgres3'.
connect to pg4@yyy.xo.jp as db4 user admin; ...connect to 'pg4' database of 'yyy.co.jp' server.
If you specify a user name without specifying a password, 'password prompt' will be displayed. If a password is not set up, please specify 'NULL' as a password, like "USER username NULL".
'USER' clause is omissible if the username is equal to the Unix login name.
(Example without setting up a password)
connect to postgres_db user xxx;
password: ................. input only ENTER key.
connect to postgres_db user xxx NULL;
... This case, password: prompt is not displayed.
|
In addition, CONNECT statement can connect to the same database using some different connection names. By using this functionality, you can easily make a environment that some users connect to the same database.
ex)
connect to postgres_db as db1;
connect to postgres_db as db2;
|
- Disconnect the database
DISCONNECT statement can disconnect the connection. 'DISCONNECT all' can disconnect all the databases.
DISCONNECT connect_name|CURRENT|DEFAULT|ALL;
connect_name.....connection name
CURRENT..........current connection
DEFAULT..........connection using 'CONNECT TO DEFAULT'
ALL..............all the connections
example)
disconnect db4;
disconenct all;
If Pgbash stops in the interactive environment or if the shell script stops,
all the connections are disconnected automatically.
Therefore, it is allowed that DISCONNECT statement is not described.
- Change the current connection
When connecting to some databases, you can change the current connection using 'set connection' statement.
SET CONNECTION connect_name|DEFAULT;
connect_name.....connection name
DEFAULT..........connection name using 'CONNECT TO DEFAULT'
example)
connect to postgres1 as db1;
connect to postgres2 as db2;
connect to postgres1 as db3;
set connection db1;
exec_sql -d db2 "select * from test2".... connect to the db2 temporary
exec_sql "select * from test1"
exec_sql -d db3 "select * from test2"
select * from test;
disconnect all;
Moreover, you can change the connection of the database by using exec_sql -d option.
- Display the connection status
'exec_sql -m' can display the status of connecting to the database.
In the interactive environment, 'exec_sql -m' is executed by only inputting '?b'.
example)
connect to default;
connect to postgres@www2.psn.ne.jp:5432 as db1;
connect to admin@xxx.psn.ne.jp as db2 user admin;
exec_sql -mL (or ?m )
# Connected Databases List (C: current database is '*')
+---+--------------+-----------+------------------------------+
| C | connect_name | user_name | target_name |
+---+--------------+-----------+------------------------------+
| | _DEFAULT_ | postgres | postgres:5432 |
| | db1 | postgres | postgres@www.psn.ne.jp:5432 |
| * | db2 | admin | admin@xxx.psn.ne.jp:5432 |
+---+--------------+-----------+------------------------------+
(3 rows)
|