Does db_link in postgres auto close connection when used with this format? How to auto close postgres dblink?

Question

I have a query that connects to remote database and brings back results bit it in a function as shown below.

CREATE OR REPLACE FUNCTION get_users()
$BODY$   
BEGIN
    RETURN QUERY
SELECT c.user_id, c.user_name, c.user_subscrib 
FROM dblink('remote_db1', 'select user_id,  user_name, user_subscrib from users_tbl') 
AS c(user_id int, user_name varchar, user_subscrib varchar);

END;
$BODY$;
LANGUAGE plpgsql;

Once this function is called like select * from get_users(); it returns the result of the inner query which connects to remote database, executes and conveniently brings the results back. My question is, do I have to close the dblink or it will automatically close?

Thank you in advance for your help.

Answer 1

Assuming that you have previously used dblink_connect to connect and named the connection "remote_db1" then you will need to do a dblink_disconnect on the named connection.

PostgreSQL dblink

If you include the connection string itself in place of using the named connection then the connection will only last as long as the query and then close itself.

Answer 2

I am answering this question. Yes it does automatically close connection. You do not need to use db_link connect/disconnect explicitly when you name the dblink your connecting within the query as shown above.