Search code examples
sqlpostgresqlplpgsqlpg

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

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.

Solution

  • 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.