You could use a data-modifying CTE (introduced with PostgreSQL 9.1) for that to perform well. Consider the following demo:
CREATE OR REPLACE FUNCTION f_fetch3()
RETURNS text AS
$func$
SELECT public.dblink_connect(
'hostaddr=?.?.?.? port=5432 dbname=db user=postgres password=???');
-- Truncate tables first?
-- TRUNCATE tbl1, tbl2, tbl3;
WITH i AS (
SELECT *
FROM public.dblink(
'SELECT id
,col1
,col2
,col3
FROM remote_tbl'
) AS d (id int, col1 text, col2 text, col3 text)
)
, x AS (
INSERT INTO tbl1
SELECT id, col1
FROM i
)
, y AS (
INSERT INTO tbl2
SELECT id, col2
FROM i
)
INSERT INTO tbl3
SELECT id, col3
FROM i;
/* -- Analyze tables?
ANALYZE tbl1;
ANALYZE tbl2;
ANALYZE tbl3;
*/
SELECT public.dblink_disconnect();
$func$ LANGUAGE sql VOLATILE SECURITY DEFINER
SET search_path=myschema, pg_temp;
ALTER FUNCTION f_fetch3() OWNER TO postgres;
REVOKE ALL ON FUNCTION f_fetch3() FROM public;
Call:
SELECT f_fetch3();
Tested with PostgreSQL 9.1.4.
If you put your password here, I would create that function in a separate schema and remove all access from schema and function from the general public.
You obviously have dblink installed, but for the general public: the extension has to be installed once per database:
CREATE EXTENSION dblink;