原 postgresql使用dblink实现跨库查询，更新等操作

版权声明：本文为博主原创文章，请尊重他人的劳动成果，转载请附上原文出处链接和本声明。
本文链接：https://www.91mszl.com/zhangwuji/article/details/1472

一：安装扩展库

create extension if not exists dblink;

二：使用dblink实现跨库新增

<insert id="insertStore">
        insert into retail.mszl_business_stores(store_id, store_name, create_time)
            select * from dblink('host=192.168.0.100 port=5432 dbname=MSZL user=postgres password=123456','select tx_id, title, now() as createTime from retail.base_store_type_all')
                as t (tx_id varchar, title varchar, createTime TIMESTAMP)
</insert>

三：使用dblink实现跨库修改

<update id="updateStore" parameterType="java.util.Map">
    select dblink_connect('my_connect','host=192.168.0.100 port=5432 dbname=MSZL user=postgres password=123456');

    update retail.mszl_business_stores c set
        org_id = s.org_id,
        org_id_all = s.org_ids,
        store_name = s.store_name
    from (
        select * from dblink('my_connect','select store_code, store_name, org_id, org_ids from retail.kk_store')
            as t (store_code varchar, store_name varchar, org_id varchar, org_ids varchar)
	) s where c.store_code=s.store_code;

    select dblink_disconnect('my_connect');
</update>

四：如果不想写死连接postgresql的信息，可以参考如下方式进行动态传参

<update id="updateStore" parameterType="java.util.Map">
    select dblink_connect('my_connect','host=${hostName} port=${port} dbname=${dbname} user=${userName} password=${password}');

    update retail.mszl_business_stores c set
        org_id = s.org_id,
        org_id_all = s.org_ids,
        store_name = s.store_name
    from (
        select * from dblink('my_connect','select store_code, store_name, org_id, org_ids from retail.kk_store')
            as t (store_code varchar, store_name varchar, org_id varchar, org_ids varchar)
	) s where c.store_code=s.store_code;

    select dblink_disconnect('my_connect');
</update>