postgresql 跨库访问外部表 存储过程

一、建环境1、创建一个拓展    create extension dblink;   若执行上述sql报错：ERROR:  could not open extension control file "/usr/share/postgresql/9.3/extension/tablefunc.control": No such file or directory   则执行下面命令来安装相关依赖：sudo apt-get install postgresql-contrib2、通过相应的函数来建立连接    select dblink_connect('test_dblink','dbname=xxxx host=xx.xx.xx.xx port=xxxx user=xxxx password=xxxx');3、通过指定的函数来获取相关跨库数据    select * from dblink('test_dblink','select update_time, id from t_account_config') as test(a timestamp, b int);4、关闭边接    select dblink_disconnect('test_dblink');

二、存储过程-- Function: dblink_get_phone_info()-- DROP FUNCTION dblink_get_phone_info();CREATE OR REPLACE FUNCTION dblink_get_phone_info()  RETURNS boolean AS$BODY$DECLARE recordRow RECORD;DECLARE lastUpdateTime timestamp;DECLARE querySqlStr text;DECLARE lastUpdateTimeStr text;BEGIN-- 获取当前本库数据最后更新时间SELECT create_time INTO lastUpdateTime FROM t_phone_consume_statistic ORDER BY create_time DESC LIMIT 1;-- 本地无数据，则IF lastUpdateTime IS NULL THENSELECT now() INTO lastUpdateTime;END IF;-- 将时间转为字符串lastUpdateTimeStr:=to_char(lastUpdateTime, 'YYYY-MM-DD HH12:MI:SS.MS');-- 拼接查询数据库querySqlStr:= 'select phone_imsi, phone_number, phone_add_time from t_phone where phone_add_time > '  || quote_literal(lastUpdateTimeStr)  || ' order by phone_add_time DESC limit ' || 100;-- 建立跨库连接PERFORM dblink_connect('dblink','dbname=xxxx host=xx.xx.xx.xx port=xxxx user=xxxx password=xxxx');-- 循环写入获得数for recordRow IN select * from dblink('dblink', querySqlStr) as data(phoneImsi bigint, phoneNumber text, addTime timestamp) LOOP-- 手机号为空则不插入IF recordRow.phoneNumber IS NOT NULL THENINSERT INTO t_phone_consume_statistic (phone_num, imsi, create_time) VALUES (recordRow.phoneNumber, recordRow.phoneImsi, recordRow.addTime);END IF;END LOOP;-- 断开跨库连接PERFORM dblink_disconnect('dblink');RETURN TRUE;END$BODY$  LANGUAGE plpgsql VOLATILE  COST 100;ALTER FUNCTION dblink_get_phone_info()  OWNER TO postgres;