Inserting records into a Table in Remote database from another table in remote database

This is a multi-part message in MIME format.

------_=_NextPart_001_01CA4E2F.7C00B41E
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

I am new to PostgreSQL. I am using PostgreSQL 8.4.

[Requirement]:

I want to move all the records from a table in a database say, 'db_one'
into a table in another database say, 'db_two'. This I need to do by
executing a stored function that I stored in the 'postgres' database.

How can I effectively do this?

[Additional information]

1. 'db_one' and 'db_two' are present in the same server (under
localhost:5432 ) as that of the 'postgres' database.

2. Table in both the databases db_one & db_two are identical (i.e.
has same number of columns, column name, type etc.).

Here's what I tried to do,

select dblink_connect('connection_to_db_one', 'host=3Dlocalhost port=3D54=
32
user=3Dpostgres dbname=3Ddb_one password=3D*****');

select dblink_connect('connection_to_db_two', 'host=3Dlocalhost port=3D54=
32
user=3Dpostgres dbname=3Ddb_two password=3D*******');

select * from dblink('connection_to_db_one','select * from
db_one_table') as temp_table(user_id integer,

"MinTimestamp" timestamp without time zone, "MaxTimestamp" timestamp
without time zone);

PERFORM dblink_exec('connection_to_db_two','insert into
db_two_table(temp_table)');

select dblink_disconnect('connection_to_db_one');

select dblink_disconnect('connection_to_db_two');

I attempted the above code snippet from inside a stored function and
happened to receive the following error:

ERROR: query has no destination for result data

Kindly provide any help/suggestions.

Thanks & Regards,

Vishnu S

***** Confidentiality Statement/Disclaimer *****

This message and any attachments is intended for the sole use of the inte=
nded recipient. It may contain confidential information. Any unauthorized=
use, dissemination or modification is strictly prohibited. If you are no=
t the intended recipient, please notify the sender immediately then delet=
e it from all your systems, and do not copy, use or print. Internet commu=
nications are not secure and it is the responsibility of the recipient to=
make sure that it is virus/malicious code exempt.

The company/sender cannot be responsible for any unauthorized alterations=
or modifications made to the contents. If you require any form of confir=
mation of the contents, please contact the company/sender. The company/se=
nder is not liable for any errors or omissions in the content of this mes=
sage.

------_=_NextPart_001_01CA4E2F.7C00B41E
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-mi=
crosoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:wo=
rd" xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D=
"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Hi,<o:p></o:p>

<o:p> </o:p>

I am new to PostgreSQL. I am using PostgreSQL =
8.4. <o:p></o:p>

<o:p> </o:p>

[Requirement]:<o:p></o:p>

I want to move all the records from a table in a dat=
abase say,
‘db_one’ into a table in another database say, ‘d=
b_two’.
This I need to do by executing a stored function that I stored in t=
he ‘postgres’
database.<o:p></o:p>

How can I effectively do this?<o:p></o:p>

<o:p> </o:p>

[Additional information]<o:p></o:p>

<![if !supportLists]>1.=

<![endif]>‘db_one’ and ‘db_two̵=
7; are
present in the same server (under localhost:5432 ) as that of the ‘=
postgres’
database.<o:p></o:p>

<![if !supportLists]>2.=

<![endif]>Table in both the databases db_one & db_two a=
re identical
(i.e. has same number of columns, column name, type etc.).<o:p></o:p>=

<o:p> </o:p>

Here’s what I tried to do, <o:p></o:p></=
p>

<o:p> </o:p>

select
dblink_connect('connection_to_db_one', 'host=3Dlocalhost port=3D5432 user=
=3Dpostgres
dbname=3Ddb_one password=3D*****');<o:p></o:p>

select
dblink_connect('connection_to_db_two', 'host=3Dlocalhost port=3D5432 user=
=3Dpostgres
dbname=3Ddb_two password=3D*******');<o:p></o:p>

<o:p> </o:p></spa=
n>

select * from
dblink('connection_to_db_one','select * from db_one_table') as
temp_table(user_id integer,<o:p></o:p>

"MinTimestamp&quo=
t;
timestamp without time zone, "MaxTimestamp" timestamp without t=
ime
zone);<o:p></o:p>

<o:p> </o:p></spa=
n>

PERFORM
dblink_exec('connection_to_db_two','insert into db_two_table(temp_table)'=
);<o:p></o:p>

<o:p> </o:p></spa=
n>

select
dblink_disconnect('connection_to_db_one');<o:p></o:p>

select
dblink_disconnect('connection_to_db_two');<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

I attempted the above code snippet from inside a sto=
red function
and happened to receive the following error:<o:p></o:p>

ERROR: query has no
destination for result data<o:p></o:p>

<o:p> </o:p>

Kindly provide any help/suggestions.<o:p></o:p>

<o:p> </o:p>

Thanks
& Regards,<o:p></o:p>

Vishnu S<o:p></o:p>

</div>

***** Confidentiality Statement/Disclaimer ***** This message and=
any attachments is intended for the sole use of the intended recipient. =
It may contain confidential information. Any unauthorized use, disseminat=
ion or modification is strictly prohibited. If you are not the intended r=
ecipient, please notify the sender immediately then delete it from all yo=
ur systems, and do not copy, use or print. Internet communications are no=
t secure and it is the responsibility of the recipient to make sure that =
it is virus/malicious code exempt. The company/sender cannot be re=
sponsible for any unauthorized alterations or modifications made to the c=
ontents. If you require any form of confirmation of the contents, please =
contact the company/sender. The company/sender is not liable for any erro=
rs or omissions in the content of this message. </body>

</html>

------_=_NextPart_001_01CA4E2F.7C00B41E--