如何在 postgresql 函数过程中使用 postgres_fdw从本地主机连接到多个远程服务器

Question

我想从本地postgresql过程连接到（SELECT）远程服务器，以便将此表存储在localhost中。我尝试使用这个：

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

但是得到这个错误：

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

很明显，该参数仅接受字符串"127.0.0.1"，例如，建立连接，但我希望此过程连接到多个服务器。所以这就是我将参数编码为变量ip_server的原因。

声明变量如下所示：

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

有没有其他方法可以实现多个连接？

我这样做是因为需要在每台服务器中执行这些过程，因为此过程不是在任何服务器中创建的......也许有一种方法可以在每个服务器中发送和创建过程。密码参数也有同样的问题。

注意：所有服务器都具有相同的数据库结构，它只更改id，密码和IP。

谢谢

Answer 1

您必须在 plpgsql 函数中使用动态 SQL，才能根据不同的参数动态创建连接。在同一函数中创建服务器和用户映射将很方便，例如：

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

用法示例：

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)

您可能还想在函数体中创建外部表，对函数进行适当的修改应该很容易。