This is a multi-part message in MIME format.
------_=_NextPart_001_01C8E349.3BCEB1EB
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Hello,
i have several machines having the same database layout. Now i want
im****t data from a remote machine.=20
I tried something like this:
CREATE OR REPLACE FUNCTION im****t_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 :=3D 'hostaddr=3D' || v_host || ' dbname=3Dconfig
user=3Duser password=3Dpass';
-- 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))=20
LOOP
INSERT INTO config (id, name) values
(v_config.id, v_config.name);
END LOOP;
PERFORM 'SELECT dblink_disconnect (''con'');';
=09
v_return :=3D 4;
RETURN v_return;
END;
$$ LANGUAGE 'plpgsql';
But now I get:
NOTICE: connected
ERROR: could not establish connection
DETAIL: missing "=3D" after "'con'" in connection info string
CONTEXT: PL/pgSQL function "im****t_sc" line 13 at FOR over SELECT rows
********** Fehler **********
ERROR: could not establish connection
SQL Status:08001
Detail:missing "=3D" after "'con'" in connection info string
Kontext:PL/pgSQL function "im****t_sc" line 13 at FOR over SELECT rows
What does that mean?
Thank you very much.
Tobias
------_=_NextPart_001_01C8E349.3BCEB1EB
Content-Type: text/html;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7652.24">
<TITLE>Dataim****t from remote db</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><FONT SIZE=3D2 FACE=3D"Arial">Hello,</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">i have several machines having the same =
database layout. Now i want im****t data from a remote machine. </FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">I tried something like this:</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">CREATE OR REPLACE FUNCTION =
im****t_sc(text)</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> RETURNS int4 AS $$</FONT>
<BR> <FONT SIZE=3D2 =
FACE=3D"Arial">DECLARE</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">v_host =
ALIAS FOR $1;</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">v_return =
int4;</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">v_constr =
text;</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">v_config =
record;</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">temp =
record;</FONT>
<BR> <FONT SIZE=3D2 =
FACE=3D"Arial">BEGIN</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">v_constr :=3D 'hostaddr=3D' || v_host || ' =
dbname=3Dconfig user=3Duser password=3Dpass';</FONT>
</P>
<P> =
<FONT SIZE=3D2 =
FACE=3D"Arial">-- connect to remote database</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">PERFORM 'SELECT dblink_connect(''con'', =
v_constr);';</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">RAISE NOTICE 'connected';</FONT>
</P>
<P> =
<FONT SIZE=3D2 =
FACE=3D"Arial">FOR v_config in (SELECT * from dblink('''con''', =
'SELECT * from config ') AS</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial"> hc(id character(36), name text)) </FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">LOOP</FONT>
<BR> =
=
<FONT SIZE=3D2 =
FACE=3D"Arial">INSERT INTO config (id, name) values (v_config.id, =
v_config.name);</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">END LOOP;</FONT>
</P>
<P> =
<FONT SIZE=3D2 =
FACE=3D"Arial">PERFORM 'SELECT dblink_disconnect (''con'');';</FONT>
<BR> =
=20
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">v_return :=3D 4;</FONT>
<BR> =
<FONT SIZE=3D2 =
FACE=3D"Arial">RETURN v_return;</FONT>
</P>
<P> <FONT SIZE=3D2 =
FACE=3D"Arial">END;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">$$ LANGUAGE 'plpgsql';</FONT>
</P>
<BR>
<P><FONT SIZE=3D2 FACE=3D"Arial">But now I get:</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">NOTICE: connected</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">ERROR: could not establish =
connection</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">DETAIL: missing "=3D" =
after "'con'" in connection info string</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">CONTEXT: PL/pgSQL function =
"im****t_sc" line 13 at FOR over SELECT rows</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">********** Fehler **********</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">ERROR: could not establish =
connection</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">SQL Status:08001</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Detail:missing "=3D" after =
"'con'" in connection info string</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Kontext:PL/pgSQL function =
"im****t_sc" line 13 at FOR over SELECT rows</FONT>
</P>
<BR>
<P><FONT SIZE=3D2 FACE=3D"Arial">What does that mean?</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial">Thank you very much.</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Tobias</FONT>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C8E349.3BCEB1EB--
|
