Re: Feature request: Connection string parsing for postgres_fdw

2020年12月23日(水) 22:05 Eric Hanson <eric@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
connectionstyles 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
neithera connection URI, nor a keyword/value string.  One could imagine writing some parser that extracts all the
variablesand 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
variablesin a given connection string, so that I can then pipe those into CREATE SERVER options.  Either that, or teach
postgres_fdwhow 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