一、建环境
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-contrib
2、通过相应的函数来建立连接
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 THEN
SELECT 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 THEN
INSERT 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;