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-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;