Handling Foreign Data Wrapper (FDW) Inaccessibility in PostgreSQL Join

Question

I have a PostgreSQL database "local_db" with a foreign data wrapper (FDW) connection to an external database "foreign_db". In the external database, there is a table called Pricing(product_id, price). I am attempting to join a local table called Product with the Pricing table using the following query:

SELECT Product.*, COALESCE(foreign_db.Pricing.price, 0) AS price
FROM Product 
LEFT JOIN foreign_db.Pricing ON Product.id = foreign_db.Pricing.product_id;

This query works well when the FDW connection is accessible. However, I am looking for a dynamic solution that gracefully handles cases where the FDW database is inaccessible. Currently, if the FDW is not accessible, the query throws an exception or error.

I want the query to return null for the price column if the FDW database is unreachable, rather than throwing an error. Is there a way to achieve this in PostgreSQL? Perhaps a conditional check or configuration setting that can be implemented to handle FDW inaccessibility without interrupting the query execution?

So far, I managed to create a function that returns empty rows if the database if unreachable

CREATE OR REPLACE FUNCTION get_prices()
RETURNS TABLE (
    product_id VARCHAR(5),
    price NUMERIC(11,2)
)
AS $$
BEGIN
    RETURN QUERY EXECUTE 'SELECT * FROM foreign_db.Pricing';
EXCEPTION
    WHEN OTHERS THEN
        RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

then calling it like:

SELECT Product.*, COALESCE(Pricing.price, 0) AS price FROM Product
LEFT JOIN get_prices() Pricing
on Pricing.product_id = Product.product_id

However, this is not a dynamic solution since it will produce error if the columns of Pricing are changed.

I appreciate any insights, suggestions or entirely different approaches on how to achieve this dynamic handling of FDW inaccessibility.