Обсуждение: Dataimport from remote db

Hello,

i have several machines having the same database layout. Now i want
import data from a remote machine.

I tried something like this but maybe there is a better way in doing
such a thing???

CREATE OR REPLACE FUNCTION import_sc(text)
  RETURNS int4 AS $$
    DECLARE
        v_host        ALIAS FOR $1;
        v_return    int4;
        v_constr    text;
        v_config        record;
        temp        record;
    BEGIN
        v_constr := 'hostaddr=' || v_host || ' dbname=config
user=user password=pass';

        -- connect to remote database
        PERFORM 'SELECT dblink_connect(''con'', v_constr);';
        RAISE NOTICE 'connected';

        FOR v_config in (SELECT * from  dblink('''con''',
'SELECT * from config ') AS
          hc(id character(36), name text))
        LOOP
            INSERT INTO config (id, name) values
(v_config.id, v_config.name);
        END LOOP;

        PERFORM 'SELECT dblink_disconnect (''con'');';

        v_return := 4;
        RETURN v_return;

    END;
$$ LANGUAGE 'plpgsql';


But now I get:
NOTICE:  connected

ERROR:  could not establish connection
DETAIL:  missing "=" after "'con'" in connection info string

CONTEXT:  PL/pgSQL function "import_sc" line 13 at FOR over SELECT rows

********** Fehler **********

ERROR: could not establish connection
SQL Status:08001
Detail:missing "=" after "'con'" in connection info string
Kontext:PL/pgSQL function "import_sc" line 13 at FOR over SELECT rows


What does that mean?

Thank you very much.
Tobias

"Schoenit, Tobias  AT/BHL-ZTPS" <tobias.schoenit@schaeffler.com> writes:
>         -- connect to remote database
>         PERFORM 'SELECT dblink_connect(''con'', v_constr);';
>         RAISE NOTICE 'connected';

>         FOR v_config in (SELECT * from  dblink('''con''',
> 'SELECT * from config ') AS

> But now I get:
> NOTICE:  connected

> ERROR:  could not establish connection
> DETAIL:  missing "=" after "'con'" in connection info string

You've got too many quotes in the second dblink call (as indeed the
error message shows, if you look carefully).

I'm also pretty sure that the first PERFORM isn't really establishing a
connection --- it looks to me like all it's doing is evaluating a
constant string.  You seem to be confused about the difference between
PERFORM and EXECUTE.  There is no need for EXECUTE here, so this
would be sufficient:
    PERFORM dblink_connect('con', v_constr);

            regards, tom lane