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

Поиск
Список
Период
Сортировка
От Vishnu S.
Тема Inserting records into a Table in Remote database from another table in remote database
Дата
Msg-id 9A1299C7A40D7447A108107E951450CA12803329@MAIL-TVM.tvm.nestgroup.net
обсуждение исходный текст
Список pgsql-admin

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

***** 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, dissemination or modification is strictly prohibited. If you are not the intended recipient, please notify the sender immediately then delete it from all your systems, and do not copy, use or print. Internet communications 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 confirmation of the contents, please contact the company/sender. The company/sender is not liable for any errors or omissions in the content of this message.

В списке pgsql-admin по дате отправления:

Предыдущее
От: idris khanafi
Дата:
Сообщение: Error when running PG_DUMP
Следующее
От: "Neha Patel"
Дата:
Сообщение: Urgent Help required