Foreign Data Wrapper for PostgreSQL Part 2 : Create Server , User Mapping and Foreign Table

After installation of foreign data wrapper which can be read in this blog post , in order to access data from PostgreSQL database we must create server, user mapping and foreign table.

Creation of server provides connection information of foreign data resource that foreign data wrapper uses to access.

create server oracle_SERVER foreign data wrapper oracle_fdw options (dbserver '//10.1.X.X/DB_INSTANCE_NAME');

Next step which is creation of user mapping contains a mapping of user to a foreign server.

create user mapping for $POSTGRESQL_USER server oracle_SERVER options(user 'oracle_user',password 'oracle_user_password');

$POSTGRESQL_USER is the PostgreSQL database user which is mapped to foreign server.

Foreign server owner can create user mapping for any user in server.Also a user which has usage privilege for foreign server can create user mapping for his own user.

Finally,foreign table is created with following command,

create foreign table foreign_table (user_id numeric,user_name text) server oracle_SERVER options (schema 'oracle_schema_name',table 'oracle_table');

With psql interface you can select foreign_table.

user_id and user_name are table fields which are identical to Oracle table

Source :
http://www.postgresql.org/docs/9.1/static/sql-createserver.html
http://www.postgresql.org/docs/9.1/static/sql-createusermapping.html
http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html