PostgreSQL CREATE SERVER with dynamic (non constant) options?

Question

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?

You need dynamic SQL. Compose a query string in your client programming language or using PL/pgSQL on the database server. — Laurenz Albe, Mar 01 '23 at 07:01

score 0 · Answer 1 · answered Mar 03 '23 at 14:57

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
$_$;

PostgreSQL CREATE SERVER with dynamic (non constant) options?

1 Answers1