Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table.

But now I was thinking of creating a function to do the whole procedure including inserting the data into the three tables. I guess am stuck here...

share|improve this question
    
pgsql_fdw can do this job also! –  francs Jul 26 '12 at 8:20
add comment

1 Answer

up vote 3 down vote accepted

You could use a writeable CTE (introduced with PostgreSQL 9.1) for that to perform well. Consider the following demo:

CREATE OR REPLACE FUNCTION f_fetch3()
  RETURNS text AS
$BODY$

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

$BODY$ 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;
share|improve this answer
    
Your solution looks good, but it throws the following error when I try to run it ERROR: schema "x" does not exist. am using "PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 32-bit" what could be the problem @Erwin –  G. Ngotho Jul 25 '12 at 19:17
    
@user1328181: That's an artifact from my first draft. I tested locally in a schema named x, and my answer contained the schema-qualification at first, but I removed it a bit later as you can see in the edit history. You generally have to adjust for local schema names - for instance, I am assuming that dblink is installed to the schema public. –  Erwin Brandstetter Jul 25 '12 at 19:19
    
Thanks, It worked perfectly>>> –  G. Ngotho Jul 25 '12 at 20:01
    
@user1328181: Nice! :) –  Erwin Brandstetter Jul 25 '12 at 20:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.