Search code examples
postgresqlpostgres-fdw

PostgreSQL CREATE SERVER with dynamic (non constant) options?

Using PostgreSQL 12 (or greater).

Is there a way to use SQL, functions, or properties etc to fill in OPTIONS when creating a FDW server?

I.e.

I want to do something like:

CREATE SERVER my_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS(
  dbname 'the_database',
  user CURRENT_USER,
  host current_setting('remote.host'), 
  port inet_server_port()::TEXT);

Any way to do something like this or configure servers/user mappings another way WITHOUT using fixed constants?

Solution

  • You could try a dynamic SQL like this:

    --SELECT set_config('remote.host','pg.farfaraway.com',true);


DO LANGUAGE PLPGSQL
$_$
BEGIN

EXECUTE FORMAT('
CREATE SERVER my_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS(
  dbname %L,
  user %L,
  host %L, 
  port %L);
','the_database',CURRENT_USER,current_setting('remote.host'),inet_server_port()::TEXT) ;

END
$_$;