Friday, June 8, 2012

Postgresql dblink connect with random port


I was working in a project where I have to build a function which will use dblink to connect to other Postgresql databases. It could be easily done unless my client wanted to use port address dynamically from the function. More precisely, he would going to connect to other databases using dblink_connect and the critical part was in his environment different Postgresql clusters were running in different ports. He was the project owner and he told me that the developers had no idea in which ports the Postgresql instances were running. So he wanted me to write a function which would find out the port of the Postgresql instance from where the dblink_connect had been called. The developer would pass the database name, the user name and the password and my function would use these variables to make the developer connect for operation in that Postgresql instance.

So I build the function as below :-

CREATE OR REPLACE FUNCTION dblink_connect_with_dynamic_port(prm_dbname text, prm_user_name text, prm_password text) RETURNS text AS $$
      str VARCHAR(100);
      v_port int;
       execute 'show port' into v_port ;
       str := '''dbname = '||prm_dbname||' user= '||prm_user_name||' password = '||prm_password||'  port=' ||v_port || '''';
      str :=  'select dblink_connect(\'new_conn\','||str||')';
       execute str;
       return str;
$$ LANGUAGE plpgsql;

Leave a Reply