I've been working on a major project that compares data from multiple data sources. I've been using postgres_fdw for this and I've been successful in retrieving data to my local postgres instance from multiple remote postgres servers.
My next step is to do the exact same thing, but to connect to a remote SQL Server. Below is my code that I have used for connecting to remote postgres servers, although I have removed the connection and user details.
CODE
CREATE EXTENSION
IF
NOT EXISTS postgres_fdw;
CREATE SERVER
IF
NOT EXISTS remote_mleci_prod FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( HOST '<HOST>', PORT '<PORT>', dbname'<DBNAME>' );
CREATE USER MAPPING
IF
NOT EXISTS FOR postgres SERVER remote_mleci_prod OPTIONS ( USER '<DB USER>', PASSWORD '<DB PASSWORD>' );
GRANT USAGE ON FOREIGN SERVER remote_mleci_prod TO local_user;
IMPORT FOREIGN SCHEMA PUBLIC
LIMIT TO ( mle_object, source_mle_mapping, source_object, mle_enrolments )
FROM
SERVER remote_prod_dda INTO PUBLIC;
SELECT
mo.mle_object_id AS "MLE OBJECT ID",
mo.mle_id AS "COURSE ID",
so.source_object_id AS "SOURCE OBJECT ID",
so.source_id AS "SOURCE ID"
FROM
mle_object mo
LEFT JOIN source_mle_mapping smm ON smm.mle_object_id = mo.mle_object_id
LEFT JOIN source_object so ON smm.source_object_id = so.source_object_id
WHERE
mo.mle_id = 'I3132-CIVL-11130-1221-1YR-037943'
ERROR
ERROR: relation "mle_object" does not exist LINE 7: mle_object mo
The SELECT statement works when running on the actual server.
Can somebody take a look and put me on the right path?
Thanks in advance.
