I have a Postgres SQL query that retrieves all the data I need apart from one field, which is not stored. I have to connect to a SQL Server database to retrieve this additional field, which I am doing using dblink. I've used dblink in a few other queries, but only connecting Postgres to Postgres.
SELECT
cm.course_id AS "EXTERNAL_COURSE_KEY",
cm.course_id AS "COURSE_ID",
cm.course_name AS "COURSE_NAME",
cm.start_date AS "START_DATE",
'' AS "END_DATE",
cm.available_ind AS "AVAILABLE_IND",
CASE
WHEN cm.row_status = '0' THEN
'ENABLED'
WHEN cm.row_status = '1' THEN
'UNDEFINED'
WHEN cm.row_status = '2' THEN
'DISABLED' ELSE'DELETED'
END ROW_STATUS,
'TEMPLATE ID' AS "TEMPLATE_COURSE_KEY",
CASE
WHEN cm.duration = 'C' THEN
'CONTINUOUS'
WHEN cm.duration = 'R' THEN
'RANGE'
END DURATION
FROM
course_main cm
CROSS JOIN dblink ( 'host=<HOST> port=<PORT NUM> dbname=<DB NAME> user=<USERNAME> password=<PASSWORD>',
'SELECT mle_id, template_id from mle_object;' ) -- accessing sql server db
AS course_templates ( mle_id VARCHAR(255), template_id VARCHAR(255) )
WHERE
cm.course_id = 'I3016-AMBS-60037-1192-06M-027386'
When I run the query, I get the error below.
ERROR: could not establish connection DETAIL: could not connect to server: Connection timed out Is the server running on host "[HOST]" ([IP ADDRESS]) and accepting TCP/IP connections on port [PORT NUM]
I can retrieve data from this sql server database in a separate query, but not using dblink.
Any help is appreciated.
thanks