CREATE OR REPLACE FUNCTION sync_cur_data(usr text, passwd text, days integer)
RETURNS integer AS
$BODY$
DECLARE
count integer;
r cur_data%rowtype;
BEGIN
count = 0;
FOR r IN SELECT *
FROM dblink(format('dbname=origin_db user=%s password=%s', usr, passwd),
format('select * from tbl_data where ts > (current_timestamp - interval '%d days')))
AS t1 (
f1 integer,
f2 boolean,
f3 text,
ts timestamp
)
LOOP
count = count + 1;
PERFORM f1 FROM cur_data WHERE f1=r.f1;
IF NOT FOUND THEN
INSERT INTO cur_data SELECT r.*;
ELSE
UPDATE cur_data SET f2=r.f2, f3=r.f3, ts=r.ts WHERE f1=r.f1;
END IF;
END LOOP;
RETURN count;
END
$BODY$
LANGUAGE plpgsql VOLATILE
ISSUES
need to maintain an INSERT/UPDATE timestamp via a trigger.