Creating a foreign server in Postgres only if not exists

Question

As far as I know in order to create a foreign server (for a FDW) you need to do the following:

CREATE SERVER my_wrapper FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
 dbname 'wrapperDB',
 host '127.0.0.1',
 port '5432'
);")

But because I create a migration script for an existing database, I want to be able to check whether this server exists with the same name and then create in a similar approach that is used upon tables:

CREATE TABLE IF NOT EXISTS mytable ...

Or in stored procedures:

CREATE OR REPLACE FUNCTION public.myfunction(text)....

Do you have any idea how to do that?

Answer 1

Try a DO statement:

DO
$$BEGIN
 CREATE SERVER ...;
EXCEPTION
 WHEN duplicate_object THEN
 NULL; -- ignore
END;$$;

Answer 2

You can check if a foreign server exists with name 'my_server_name' using the following:

DECLARE
 srvExists boolean DEFAULT false;
BEGIN 
SELECT true INTO srvExists FROM pg_catalog.pg_foreign_server 
 WHERE lower(srvname) = lower( 'my_server_name' );
IF srvExists THEN
 RAISE NOTICE 'foreign server my_server_name exists';
ELSE
 CREATE SERVER my_server_name FOREIGN DATA WRAPPER postgres_fdw
 OPTIONS (dbname '<your DB>', host '<your host>', port '<your port');
END IF;
END;