Use value from current_settings to establish postgres_fdw connection

Question

I am trying to set up a PostgreSQL Foreign Data Wrapper and to access the credentials from current_settings so that they are not hard-coded in my source code.

Here is my code:

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
  FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
      host current_setting('db.REMOTE_DB_HOST'),
      port current_setting('db.REMOTE_DB_PORT'),
      dbname current_setting('db.REMOTE_DB_NAME')
    );

Before hand, I have set the credentials in as such:

SET db.REMOTE_DB_HOST='localhost';
SET db.REMOTE_DB_PORT='5432';
SET db.REMOTE_DB_NAME='remotedb';

Now, I can get the values without problem:

SELECT current_setting('db.REMOTE_DB_HOST') AS host, current_setting('db.REMOTE_DB_PORT') AS port, current_setting('db.REMOTE_DB_NAME') AS dbname;

yields the expected

   host    | port |  dbname 
-----------+------+----------
 localhost | 5432 | remotedb

But, when I try to establish the remote_server connection, I get the following error, without any other detail:

ERROR:  syntax error at or near "current_setting"
LINE 4:       host current_setting('db.REMOTE_DB_HOST'),

score 2 · Accepted Answer · answered Oct 20 '22 at 22:01

DDL commands do not allow interpolation. You cannot execute commands inside the CREATE SERVER command, only pass literal constants. Concatenate the command string and execute it.

You can use dynamic SQL with EXECUTE in a DO command:

CREATE EXTENSION postgres_fdw;  -- this works as is

DO                              -- dynamic SQL with EXECUTE
$$
BEGIN
   EXECUTE format('CREATE SERVER remote_server
                   FOREIGN DATA WRAPPER postgres_fdw
                   OPTIONS (host %L, port %L, dbname %L)'
                , current_setting('db.REMOTE_DB_HOST')
                , current_setting('db.REMOTE_DB_PORT')
                , current_setting('db.REMOTE_DB_NAME')
                 );
END
$$;

How to substitute a variable when creating a check constraint?

Use value from current_settings to establish postgres_fdw connection

1 Answers1