2020年12月23日(水) 22:05 Eric Hanson <e...@aquameta.com>:
>
> I'm trying to store connection to postgres_fdw in the database I want to be
> able to store the full breadth of connection styles and all the different
> types of connections that libpq supports. But having some troubles.
>
> Postgres_fdw wants options passed into CREATE SERVER, all broken out into
> separate variables, but this format is neither a connection URI, nor a
> keyword/value string. One could imagine writing some parser that extracts
> all the variables and honors collisions in the same way libpq does (like when
> both the path and the query string specify a port), but I really don't want
> to recreate that.
>
> It would be really nice if I could tap into libpq's connection string parser
> from SQL and use it to extract all the variables in a given connection
> string, so that I can then pipe those into CREATE SERVER options. Either
> that, or teach postgres_fdw how to consume standard connection strings.
Does something like this do what you're looking for?
postgres=# SELECT * FROM conninfo_parse('host=node1 user=foo');
keyword | value
---------+-------
user | foo
host | node1
(2 rows)
postgres=# SELECT * FROM conninfo_parse('postgresql://localhost:5433');
keyword | value
---------+-----------
host | localhost
port | 5433
(2 rows)
Basically a wrapper around PQconninfoParse(), I've had the code knocking around
for a while now and finally got round to packaging it into an
extension [1]. It's
on my todo list to submit a patch based on this to core, as it seems Generally
Useful To Have.
[1] https://github.com/ibarwick/conninfo
Regards
Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.com
