Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use dblink to achieve cross-database query in PostgreSQL. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

At first, I knew how to write the sql statement, but the execution failed, so I need to install the dblink extension first. These blog posts are not clear, thanks to netizens' advice, under windows, you only need to execute the sql statement "create extension dblink" under the corresponding database to ok. The above blog posts are basically about the methods of operation under linux, so I took a detour.

two。 Download the source code of PostgreSQL, but do not know where the statement is executed. It seems to be executed in shell under linux, but I have tried it in both psql and cmd. Also tried to directly copy the statements in dblink.sql to run, but failed to report an error.

3.windows is very simple, a sql to deal with the "create extension dblink".

You can see an extra dblink in the extension:

There are also some functions that begin with dblink in the function:

This sql statement seems to only call and run the dblink.control file:

3. Write the sql statement to test whether it is ok.

Cross-database query, you must establish a database connection before you can query, otherwise an error will be reported.

As shown in the figure, the connection named "unnamed" is prompted, because there are other connections, and if there is no connection, the connection not available is prompted:

Using dblink (text,text), the * parameter is the connection string, and the second parameter is the sql statement. Successful execution:

Or use dblink_connect (text) to establish a connection first, and then use dblink (text) to do cross-database query:

View connections:

Select dblink_get_connections ()

Disconnect all connections:

Select dblink_disconnect ()

Disconnect 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')

Indicates that the disconnect was successful.

4. The sql statement I need is also ok. For a joint query of three tables in two databases on the same server, first establish a connection and then make a query:

Select dblink_connect ('host=localhost dbname=cbe_userdata user=postgres password=lifc126820') Select A.idjie 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 An 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) B inner join (select * from dblink ('select cname,code as code1,ifdata,sort from tb_test_index_poi') as T2 (cname character varying, code1 character varying, ifdata character varying, sort character varying)) C on B.code=C.code1) D on A.code=D.poicode

If you can make it into a view, you need to use dblink (text,text) here. If you directly use the above sql statement, you will report that the 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 (50), 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 above is how to use dblink to realize cross-database query in the PostgreSQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.