dblink extension in PostgreSQL

The function of dblink extension in PostgreSQL is to operate another remote database in one database.

Sometimes a business needs to refer to data from other databases. We can take values ​​from the two databases and process them separately in the program. But this development efficiency and performance are not very good.
If both databases are PostgreSQL, it is very simple to use the extended DB LINK function.
For example, a data testdb1, testdb2. First, you need to add testdb1 to the dblink extension.

Example 1: Get the user name of the testdb2 user table

SELECT * FROM dblink(‘hostaddr=192.168.0.222 port=5432 dbname=testdb2 user=postgres password=postgres’,
‘SELECT user_name From people’) AS t(user_name text);

If you think it is troublesome to write a bunch of information in dblink for every query, you can solve it by building a view in testdb1.

CREATE VIEW remote_people_user_name AS
SELECT * FROM dblink(‘hostaddr=192.168.0.222 port=5432 dbname=testdb2 user=postgres password=postgres’,
‘SELECT user_name From people’) AS t(user_name text);

Then you can query the data from this view.

SELECT * FROM remote_people_user_name;

What if you need to modify the testdb2 data instead of just querying the data?

1. First execute dblink_connect to keep connected

SELECT dblink_connect(‘connection’,’hostaddr=192.168.0.222 port=5432 dbname=testdb2 user=postgres password=postgres’);

2. Execute the BEGIN command

SELECT dblink_exec(‘connection’,’BEGIN’);

3. Perform data operations (update, insert, create and other commands)

SELECT dblink_exec(‘connection’,’insert… data manipulation’);

4. Perform transaction commit

SELECT dblink_exec(‘connection’,’COMMIT’);

5. Disconnect

SELECT dblink_disconnect(‘connection’);

Thanks.