Re: SQL/MED compatible connection manager

Peter Eisentraut wrote:
> Martin Pihlak wrote:
>>> I would call it something like
>>>
>>> pg_postgresql_fdw_options_string(server, user) returns text
>>
>> Hmm, it is probably a good idea to avoid the fdw abbreviation -- the term
>> "foreign data wrapper" is already confusing enough. My suggestion:
>>
>> pg_foreign_server_conninfo(server)
>> pg_foreign_server_conninfo(server,user)
>>
>> If there are no objections, I'll whack those functions out, and bring
>> the dblink
>> patch up to date.
>
> Sure, propose some code. (Note that you can use parameter default
> values now.)
>

Proposal attached. This adds two C functions:

List *GetForeignConnectionOptions(Oid serverid, Oid userid);
char *GetForeignConnectionString(Oid serverid, Oid userid);

One for obtaining all of the connection related options as a list, and
another for transforming these options into a libpq conninfo string.
The latter should be useful for dblink (although the userid and serverid
need to be obtained first).

On top of those there are two SQL accessible functions:

pg_foreign_connection_options(server name, user name = current_user,
OUT option_class text, OUT option_name text, OUT option_value text);

pg_foreign_connection_string(server name, user name = current_user);

These should initially be restricted from ordinary users -- grant explicitly
if the user should see the connect strings. Otherwise use from security definer
functions. The pg_foreign_connection_options() exposes all of the connection
options and can be used by clients such as DBI link to construct the connect
string or equivalent. pg_foreign_connection_string() can be used for instance
by plpythonu or plperlu functions to connect to remote postgres database.

Example:

select * from pg_foreign_connection_options('foo');

option_class | option_name | option_value
--------------+-------------+--------------
server | host | localhost
server | port | 54321
server | dbname | foo
user mapping | user | bob
user mapping | password | secret
(5 rows)

select * from pg_foreign_connection_string('foo');

pg_foreign_connection_string
-------------------------------------------------------------------------
host='localhost' port='54321' dbname='foo' user='bob' password='secret'
(1 row)

Will add regression and tests if this is acceptable.

PS. I'm not sure if I nailed the "proargdefaults" syntax correctly in pg_proc.h,
for now I just copied it out from a sample function with similar arguments.

regards,
Martin