I check below link which I used and running perfectly. But I want to opposite this things.
Postgresql: dblink in Stored Functions
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?
Local database name: localdatabase
Remote Database name: remotedatabase
Can any one suggest me how can I do this?
Thanks in advance.
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'');');
Where test is a table in the remote database with the following definition:
CREATE TABLE test(
id serial
, some_text text
);
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)
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;
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.)
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.
INSERTin your case).