-- 使用超級用戶安裝
CREATE EXTENSION dblink;

-- 查看已安裝擴展
SELECT * FROM pg_extension WHERE extname = 'dblink';
-- 查看擴展函數(shù)
SELECT proname FROM pg_proc WHERE proname LIKE 'dblink%';

-- 創(chuàng)建持久連接(需超級用戶權(quán)限)
SELECT dblink_connect('myconn', 'host=192.168.1.100 dbname=remote_db user=user password=pass');
-- 創(chuàng)建一次性連接
SELECT dblink_connect('host=192.168.1.100 dbname=remote_db user=user password=pass');

-- 基本查詢
SELECT * FROM dblink('myconn', 'SELECT id, name FROM users') AS t(id int, name text);
-- 帶參數(shù)查詢
SELECT * FROM dblink('myconn', 'SELECT * FROM accounts WHERE balance > $1', ARRAY[1000]) 
AS t(account_id int, balance numeric);

-- 關(guān)閉指定連接
SELECT dblink_disconnect('myconn');
-- 關(guān)閉所有連接
SELECT dblink_disconnect_all();

-- 開始事務(wù)
SELECT dblink_exec('myconn', 'BEGIN');
-- 執(zhí)行更新
SELECT dblink_exec('myconn', 'UPDATE accounts SET balance = balance - 100 WHERE id = 1');
-- 提交或回滾
SELECT dblink_exec('myconn', 'COMMIT');
-- 或
SELECT dblink_exec('myconn', 'ROLLBACK');

-- 批量插入
SELECT dblink_send_query('myconn', 'INSERT INTO log_entries VALUES (1, ''message1''), (2, ''message2'')');
-- 檢查結(jié)果
SELECT dblink_get_result('myconn');

-- 查看當(dāng)前連接
SELECT * FROM dblink_get_connections();
-- 獲取連接狀態(tài)
SELECT dblink_get_pkey('myconn');

-- 使用外部文件存儲憑據(jù)
SELECT dblink_connect('myconn', 'host=192.168.1.100 dbname=remote_db user=user password=' || pg_read_file('/secure/path/password.txt'));

-- 創(chuàng)建安全視圖
CREATE VIEW remote_users AS
SELECT * FROM dblink('myconn', 'SELECT id, name FROM public.users') 
AS t(id int, name text);
-- 限制訪問權(quán)限
REVOKE ALL ON remote_users FROM PUBLIC;
GRANT SELECT ON remote_users TO reporting_role;

-- 強制SSL連接
SELECT dblink_connect('myconn', 'host=192.168.1.100 dbname=remote_db user=user password=pass sslmode=require');

-- 保持持久連接
SELECT dblink_connect('myconn', '...');
-- 在應(yīng)用中復(fù)用連接
-- 而不是每次查詢都新建連接

-- 使用游標獲取大數(shù)據(jù)集
SELECT dblink_open('myconn', 'mycursor', 'SELECT * FROM large_table');
SELECT * FROM dblink_fetch('myconn', 'mycursor', 1000) AS t(...); -- 每次獲取1000行
SELECT dblink_close('myconn', 'mycursor');

-- 發(fā)送異步查詢
SELECT dblink_send_query('myconn', 'SELECT * FROM large_table');
-- 稍后獲取結(jié)果
SELECT * FROM dblink_get_result('myconn') AS t(...);

ERROR: could not establish connection

-- 檢查網(wǎng)絡(luò)連通性
-- 驗證憑據(jù)是否正確
-- 檢查pg_hba.conf是否允許連接
-- 使用完整連接字符串
SELECT dblink_connect('hostaddr=192.168.1.100 port=5432 dbname=remote_db user=user password=pass');

ERROR: return type mismatch in column 1

-- 明確指定返回類型
SELECT * FROM dblink('myconn', 'SELECT id FROM users') AS t(id int);

-- 需要特殊處理大對象
SELECT lo_import(dblink('myconn', 'SELECT lo_get(oid) FROM large_objects WHERE id=1'));