Foreign Data Wrapper

FOREIGN DATA WRAPPER IN POSTGRE SQL

FDWs allow remote access to tables or queries from various external third-party databases or file structures. It allow access from application to data that is in different database format,in a non relational database,not in db at all.

EXAMPLE:

Create a database for all tables

CREATEDB mysql_fdw

Log into the database and run the following

CREATE EXTENSION mysql_fdw;

Create a server that points to a remote database

CREATE SERVER mysql_svr

FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (address ‘127.0.0.1’, port ‘3306’);

Create user connection parameters to the mysql database

CREATE USER MAPPING FOR postgres

SERVER mysql_server

OPTIONS (username ‘admin’, password ‘xyz12’);

Create a table to hold the data in PostgreSQL as foreign table

CREATE FOREIGN TABLE container1(

container_id int,

container_name text,

container_created datetime)

SERVER mysql_server

OPTIONS (dbname ‘db’, table_name ‘warehouse’);

insert new rows in table

We can perform insert,update,delete on the foreign table.

INSERT INTO container1 values (1, ‘CAMCORDER’, sysdate());

INSERT INTO container1 values (2, ‘CELLPHONE’, sysdate());

INSERT INTO container1 values (3, ‘TV’, sysdate());

— select from table

SELECT * FROM warehouse;

container_id | container_name | container_created

————–+—————-+——————–

1 | CAMCORDER | 29-MAR-16 23:33:46

2 | CELLPHONE | 29-MAR-16 23:34:25

3 | TV | 29-MAR-16 23:33:49

— delete row from table

DELETE FROM container1 where warehouse_id = 3;

— update a row of table

UPDATE container1 set container_name = ‘CAM_NEW’ where container_id = 1;

Advantages of FDWs:

1. 1.  Between PostgreSQL Databases:A common use for FDW is to manage data between a   few   Postgres databases. This resolves problems when data is used from one central database.
   2. Data Migration Needed Immediately.
   3. Data for Analysis.

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download