PostgreSQL uses dblink to implement cross-database query. postgresqldblink

PostgreSQL uses dblink to implement cross-database query. postgresqldblink

A recent SQL statement involves joint queries between tables in different databases.

At the beginning, I learned how to write SQL statements, but failed to execute them. I need to install dblink extension first. These blog posts are not clear. Thanks for your advice. In windows, you only need to execute the SQL statement "create extension dblink" in the corresponding database. The above blog posts basically talk about the operation methods in linux, so I took a detour.

2. after downloading the source code of PostgreSQL, I don't know where to execute the statement. It seems to be executed in shell of linux, but I tried both psql and cmd, it's a detour. I have also tried to directly copy the statements in dblink. SQL to run it, but I still cannot report an error.

3. In windows, it is very simple. After an SQL statement, "create extension dblink" is completed ".
You can see an additional dblink in the extension:

Some functions starting with dblink are also included in the function:

This SQL statement seems to have only called and run the dblink. control file:

3. test whether the SQL statement is OK.

For cross-database queries, you must first establish a database connection before querying. Otherwise, an error is reported.

Is the prompt connection named "unnamed", because there are other connections, if there is no connection, it will prompt connection not available:

When dblink (text, text) is used, the first parameter is the connection string, and the second parameter is the SQL statement. Executed successfully:

Alternatively, you can use dblink_connect (text) to establish a connection and then use dblink (text) for cross-database query:

View connections:

select dblink_get_connections()

Disconnect all connections:

select dblink_disconnect()

Disconnect a connection with the specified name:

select dblink_disconnect('test')

For example, a connection named 'test' has been established before:

select dblink_connect('test','host=localhost dbname=cbe_sta user=postgres password=lifc126820');

The system will prompt that the disconnection is successful.

4. Finally, the SQL statement I need is OK. In the joint query of two databases and three tables on the same server, create a connection and then query:

select dblink_connect('host=localhost dbname=cbe_userdata user=postgres password=lifc126820');select A.id,A.code as poicode,A.cname,A.geo,A.x,A.y,A.s01,A.s02,A.s03,A.updatetime,A.tbcode,D.code,D.data,D.value,D.cname as colname,D.ifdata,D.sort from tb_test_poi A inner join ((select * from dblink('select poicode,code,data,value from tb_test_data_poi') as T1(poicode character varying(50),code character varying(50),data double precision,value character varying(500))) B inner join (select * from dblink('select cname,code as code1,ifdata,sort from tb_test_index_poi') as T2(cname character varying(200),code1 character varying(50),ifdata character varying(5),sort character varying(50))) C on B.code=C.code1) D on A.code=D.poicode;

If the View works, dblink (text, text) is used here. If the preceding SQL statement is used directly, an error is reported, indicating that a connection cannot be established:

CREATE OR REPLACE VIEW vw_test_poi AS SELECT a.id, a.code AS poicode, a.cname, a.geo, a.x, a.y, a.s01, a.s02, a.s03,  a.updatetime, a.tbcode, d.code, d.data, d.value, d.cname AS colname,  d.ifdata, d.sort  FROM tb_test_poi a  JOIN (( SELECT t1.poicode, t1.code, t1.data, t1.value      FROM dblink('host=localhost dbname=cbe_userdata user=postgres password=lifc126820'::text, 'select poicode,code,data,value from tb_test_data_poi'::text) t1(poicode character varying(50), code character varying(50), data double precision, value character varying(500))) b  JOIN ( SELECT t2.cname, t2.code1, t2.ifdata, t2.sort      FROM dblink('host=localhost dbname=cbe_userdata user=postgres password=lifc126820'::text, 'select cname,code as code1,ifdata,sort from tb_test_index_poi'::text) t2(cname character varying(200), code1 character varying(50), ifdata character varying(5), sort character varying(50))) c ON b.code::text = c.code1::text) d ON a.code::text = d.poicode::text;

I found that the execution efficiency was a little slow. I found more than 10 thousand pieces of data, and it took more than two seconds. Currently, dblink is not used in my program, because you can check a table for the input parameters, and then query the information association between the other two tables, so I did it in two steps and solved the problem. The specific efficiency is not thoroughly compared. If you need to adjust it later, you can use dblink to do so. After all, the code in the program will be much simpler.