[Prev] [Menu] [Next]

Connection/Disconnection of the database


  • 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idefault value is equal to a Unix login namej
    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)
    

[Prev] [Menu] [Next]