CREATE EXTENSION IF NOT EXISTS dblink;
###如果已經有，可以在 pg 擴展表查到
SELECT * FROM pg_extension WHERE extname = 'dblink'; 
或使用\dx 
postgres=# \dx
                                                 已安裝擴展列表
        名稱        | 版本 |  架構模式  |                                  描述
--------------------+------+------------+------------------------------------------------------------------------
 adminpack          | 2.1  | pg_catalog | administrative functions for PostgreSQL
 dblink             | 1.2  | postgres   | connect to other PostgreSQL databases from within a database
 oracle_fdw         | 1.2  | postgres   | foreign data wrapper for Oracle access
 pg_stat_statements | 1.9  | postgres   | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0  | pg_catalog | PL/pgSQL procedural language

SELECT dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=xxxx user=xxxx password=xxxx') as dev;
解釋：
'local_connect'  是我自定義的連接的名稱
hostaddr=127.0.0.1  表示是本機地址
port=5432  表示使用5432端口，自行設置
dbname  表示要訪問的數(shù)據(jù)庫的名稱
user，password分別表示用戶名和密碼，根據(jù)自己配置的用戶名密碼更改

如：
postgres=# SELECT dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr') as dev;

 dev
-----

 OK
(1 行記錄)

-- 查詢所有已鏈接的dblink
select dblink_get_connections();

postgres=# select dblink_get_connections();
 dblink_get_connections
------------------------
 {local_connect}
(1 行記錄)

--跨庫查詢
SELECT num,id FROM dblink('local_connect','select num,id from hr.demotable') as t(num numeric,id integer);
SELECT * FROM dblink('local_connect','select num,id from hr.demotable') as t(num numeric,id integer);
SELECT * FROM dblink('local_connect','select * from hr.demotable') as t(num numeric,id integer);

--跨庫查詢寫入
insert into t_dblink
select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where id<1000') as t(num numeric,id integer);

####使用 dblink 函數(shù)從遠程數(shù)據(jù)庫獲取數(shù)據(jù)。 local_connect是預先配置好的遠程數(shù)據(jù)庫連接名
####dblink 中查詢語句被引號括起來，如果查詢語句本身有引號，需要多寫一個引號做轉義
####AS t()表示dblink返回的結果集定義了一個別名't'，并指定了每個列的數(shù)據(jù)類型

-- 關閉遠程連接
###在PostgreSQL中dblink是會話級別；會話斷開即dblink也關閉。當然也可以在會話中手動關閉
SELECT dblink_disconnect('local_connect');

-- 查詢所有已鏈接的dblink
select dblink_get_connections();

--直接寫 dblink 方式，預先配置好的到遠程數(shù)據(jù)庫的連接名 
SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable') as t(num numeric,id integer);


create table t_dblink as select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where 1=2') as t(num numeric,id integer);

insert into t_dblink
select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where id<1000') as t(num numeric,id integer);

explain analyze with t_temp as (select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where id<1000') as t(num numeric,id integer)) 
select a.num,a.id from t_dblink a,t_temp b where a.id=b.id;

postgres=# create temp table t_dblink as SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable') as t(num numeric,id integer);
SELECT 1000000
postgres=# select * from t_dblink;
...........

--退出后重新進去臨時表不存在
postgres=# select * from t_dblink;
錯誤:  關系 "t_dblink" 不存在
第1行select * from t_dblink;

postgres=# create view  v_dblink as SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable') as t(num numeric,id integer);
CREATE VIEW
postgres=# select * from v_dblink;
................
--退出后，重新執(zhí)行
postgres=# select * from v_dblink;

SELECT dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr') as dev;
SELECT dblink_exec('local_connect', 'create table aa(id int,name varchar(50))');
SELECT dblink_exec('local_connect', 'drop table aa');
SELECT dblink_exec('local_connect', 'insert into hr.t values (1011102,8999,''hello'',''2048-10-09''::date)');
SELECT dblink_exec('local_connect', 'delete from  hr.t values where id=1011102');