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

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=localhost port=5432 user=postgres dbname=db_one password=*****');

select dblink_connect('connection_to_db_two', 'host=localhost port=5432 user=postgres dbname=db_two password=*******');

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