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:
-
- 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.
- Data Migration Needed Immediately.
- Data for Analysis.
Free Open Source BI Platform
- AI-Powered Analytics
- Embedded Analytics
- Single Sign-On (SSO)
- Multi-Tenancy
- Paginated Canned Reporting
- Interactive Dashboard
- White Labeling