PostgreSQL:存储功能中的dblink从本地到远程数据库 [英] Postgresql: dblink in Stored Functions from local to remote database
问题描述
我检查下面我使用并运行正常的链接。但是我想相反。
I check below link which I used and running perfectly. But I want to opposite this things.
我的情况:有两个数据库。我想将一个表数据从本地数据库复制到远程数据库。我为此使用了dblink,但我对如何使用dblink存储数据感到困惑?
My scenario: Two databases are there. I want to copy one table data from local to remote database. I used dblink for this used but I am confused how to use dblink to store the data?
本地数据库名称:localdatabase
Local database name: localdatabase
远程数据库名称:remotedatabase
Remote Database name: remotedatabase
有人可以建议我该怎么做吗?
Can any one suggest me how can I do this?
推荐答案
类似以下几行应该可以:
Something like the lines below should work:
SELECT dblink_connect('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd');
-- change the connection string to your taste
SELECT dblink_exec('INSERT INTO test (some_text) VALUES (''Text go here'');');
其中 test
是远程表具有以下定义的数据库:
Where test
is a table in the remote database with the following definition:
CREATE TABLE test(
id serial
, some_text text
);
运行 dblink_exec()
后,可以检查远程数据库中的结果(或使用 dblink()
在本地,如下面的示例所示)。
After running dblink_exec()
, you can check the results in the remote database (or locally, using dblink()
, like in the example below).
SELECT * FROM dblink('SELECT id, some_text FROM test') AS d(id integer, some_text text);
id | some_text
----+--------------
1 | Text go here
(1 row)
您可以包装 dblink_exec
还要调用函数:
You can wrap your dblink_exec
call in a function as well:
CREATE OR REPLACE FUNCTION f_dblink_test_update(val text, id integer) RETURNS text AS
$body$
SELECT dblink_exec('UPDATE torles.test SET some_text=' || quote_literal($1) || ' WHERE id = ' || $2);
$body$
LANGUAGE sql;
您可以看到,甚至可以动态地构建查询字符串。 (不是我提倡这种方法,因为您必须小心,不要以这种方式将SQL注入漏洞引入您的系统。)
As you can see, you can even build your query string dynamically. (Not that I advocate this approach, since you have to be careful not to introduce a SQL injection vulnerability into your system this way.)
由于 dblink_exec
会返回一条有关其所做操作的文本消息,因此除非有以下说明,否则您必须将函数定义为 RETURNS text
dblink_exec
调用之后的其他返回值的语句。
Since dblink_exec
returns with a text message about what it did, you have to define your function as RETURNS text
unless there are other value-returning statements after the dblink_exec
call.
这篇关于PostgreSQL:存储功能中的dblink从本地到远程数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!