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

-- 启用dblink模块
CREATE EXTENSION dblink;

?

1 2 3 4

-- 创建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');

　　

?

1 2 3 4 5 6 7 8

-- 此处通过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';

　

?

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