postgresql之创建dblink 以及查询写法和关闭dblink

-- 启用dblink模块
CREATE EXTENSION dblink;

-- 创建dblink连接
-- format: 'dbname=mydb host=myhost user=myuser password=mypassword'
-- 请替换mydb, myhost, myuser, 和 mypassword 为实际的数据库名称、主机、用户和密码
SELECT dblink_connect('dblink_name','dbname=dbname host=host user=user password=password');

　　

-- 此处通过dblink查询 需要指定返回字段和类型 关联查询时需要注意连接的表字段属性
SELECT * FROM dblink('mult_data_original_dblink', 'SELECT original_id, create_time, satellite, sensor, cloud_percent, data_time, data_size, data_width, data_height, scene_num FROM mult_data_original WHERE file_name = ''MOD11A2.A2020233.h27v04.006.2020242031616.hdf''') AS t(original_id int, create_time timestamp, satellite int, sensor int, cloud_percent numeric, data_time date, data_size varchar, data_width int, data_height int, scene_num varchar)
UNION all
SELECT original_id, create_time, satellite, sensor, cloud_percent, data_time, data_size, data_width, data_height, scene_num 
FROM mult_data_original 
WHERE sensor in (42101, 40901, 44001) 
AND create_time > to_date('2023-07-01', 'YYYY-MM-DD') 
AND file_name = 'MOD11A2.A2020233.h27v04.006.2020242031616.hdf';

　

-- 断开dblink连接
SELECT dblink_disconnect();