- 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 nameidefault value is equal to a Unix login namej
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)
|