How to connect from localhost to multiple remote servers with postgres_fdw in postgresql function procedure?

Question

I want to connect (SELECT) from a local postgresql procedure to a remote server in order to store this table in localhost. I try using this:

CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ip_server, port '15432', dbname 'omstar_analysis_' || project_id);

but a get this ERROR:

ERROR:  syntax error at or near "||"
LINE 11:         OPTIONS (host ''||ip_server, port '15432', dbname 'o...

Is obvious that the parameter only accept a string '127.0.0.1', as example, to make the connection but I want this procedure connects to multiple servers. So this is the reason I code the parameter as a variable ip_server.

the declaration variable is below:

CREATE OR REPLACE FUNCTION get_cell_aggregation_cell_hour(ip_server VARCHAR, pass_server VARCHAR, project_id VARCHAR, cellkeys_list VARCHAR, itemid_list VARCHAR, busyhour_init INT, busyhour_finish INT, date_init VARCHAR, date_finish VARCHAR, aggre_obj VARCHAR, aggre_time VARCHAR)
RETURNS TABLE (DateLoaded varchar) AS $$

Is there another way to achieve multiple connections?

Im doing this because need to perform the procedures in every server as this procedure is not created in any server... Maybe there is a way to send and create the procedure in every server. Same issue for password parameter.

NOTE: All the server has the same data base structure, it only change id, passwords an IPs.

Thanks

Answer 1

You have to use dynamic SQL in a plpgsql function to dynamically create connections based on different parameters. It will be convenient to create both a server and a user mapping in the same function, e.g.:

create or replace function create_server(
    servername text, host text, port text, dbname text, 
    localuser text, remoteuser text, password text)
returns void language plpgsql as $$
begin

    execute format($ex$
        create server %I
        foreign data wrapper postgres_fdw
        options (host %L, port %L, dbname %L)
        $ex$, servername, host, port, dbname);

    execute format($ex$
        create user mapping for %I
        server %I
        options (user %L, password %L)
        $ex$, localuser, servername, remoteuser, password);

end $$;

Example usage:

select create_server(
    concat('server_', i), concat('192.168.0.', i), '5432', 'test', 
    'postgres', 'postgres', 'mysecret')
from (
    values ('112'), ('150'), ('168')
    ) v (i);

select srvname, srvoptions from pg_foreign_server;

  srvname   |                 srvoptions
------------+--------------------------------------------
 server_112 | {host=192.168.0.112,port=5432,dbname=test}
 server_150 | {host=192.168.0.150,port=5432,dbname=test}
 server_168 | {host=192.168.0.168,port=5432,dbname=test}
(3 rows)    

select * from pg_user_mapping;

 umuser | umserver |             umoptions
--------+----------+-----------------------------------
     10 |    16879 | {user=postgres,password=mysecret}
     10 |    16881 | {user=postgres,password=mysecret}
     10 |    16883 | {user=postgres,password=mysecret}
(3 rows)

You may also want to create a foreign table(s) in the function body, appropriate modifications of the function should be easy.