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
*** a/src/backend/foreign/foreign.c
--- b/src/backend/foreign/foreign.c
***************
*** 31,36 ****
--- 31,38 ----
extern Datum pg_options_to_table(PG_FUNCTION_ARGS);
+ extern Datum pg_foreign_connection_string(PG_FUNCTION_ARGS);
+ extern Datum pg_foreign_connection_options(PG_FUNCTION_ARGS);
/* list of currently loaded foreign-data wrapper interfaces */
***************
*** 321,338 **** GetUserMapping(Oid userid, Oid serverid)
}
/*
! * deflist_to_tuplestore - Helper function to convert DefElem list to
* tuplestore usable in SRF.
*/
static void
! deflist_to_tuplestore(ReturnSetInfo *rsinfo, List *options)
{
ListCell *cell;
TupleDesc tupdesc;
Tuplestorestate *tupstore;
! Datum values[2];
! bool nulls[2] = { 0 };
MemoryContext per_query_ctx;
MemoryContext oldcontext;
--- 323,447 ----
}
+ /*
+ * Helper for appending a ForeignConnectionOption node to a list.
+ */
+ static List *
+ append_option_list(List *options, GenericOptionFlags type, DefElem *def)
+ {
+ ForeignConnectionOption *opt = makeNode(ForeignConnectionOption);
+
+ opt->opttype = type;
+ opt->option = def;
+ return lappend(options, opt);
+ }
+
+
+ /*
+ * GetForeignConnectionOptions - look up the options for foreign connection.
+ *
+ * Foreign connection is defined by the foreign data wrapper, server and
+ * user mapping triple. The options are simply merged together into a list
+ * of ForeignConnectionOption nodes.
+ *
+ * The role specified by userid must have a user mapping and USAGE privilege
+ * on the server.
+ */
+ List *
+ GetForeignConnectionOptions(Oid serverid, Oid userid)
+ {
+ ForeignServer *server;
+ UserMapping *um;
+ ForeignDataWrapper *fdw;
+ List *result = NIL;
+ ListCell *cell;
+ AclResult aclresult;
+
+ server = GetForeignServer(serverid);
+ um = GetUserMapping(userid, serverid);
+ fdw = GetForeignDataWrapper(server->fdwid);
+
+ /* Check permissions, user must have usage on the server. */
+ aclresult = pg_foreign_server_aclcheck(serverid, userid, ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_FOREIGN_SERVER, server->servername);
+
+ /* Seems OK, prepare a list of all the options */
+ foreach (cell, fdw->options)
+ result = append_option_list(result, FdwOpt, lfirst(cell));
+ foreach (cell, server->options)
+ result = append_option_list(result, ServerOpt, lfirst(cell));
+ foreach (cell, um->options)
+ result = append_option_list(result, UserMappingOpt, lfirst(cell));
+
+ return result;
+ }
+
+
+ /*
+ * GetForeignConnectionString - return a libpq conninfo string for the
+ * foreign connection.
+ *
+ * Currently this just means transforming all of the fdw, server and user
+ * mapping options into a single string. No attempt is made to check if the
+ * resulting conninfo string is valid.
+ */
+ char *
+ GetForeignConnectionString(Oid serverid, Oid userid)
+ {
+ StringInfo stringptr = makeStringInfo();
+ ListCell *cell;
+
+ foreach (cell, GetForeignConnectionOptions(serverid, userid))
+ {
+ ForeignConnectionOption *opt = lfirst(cell);
+
+ appendStringInfo(stringptr, "%s%s='%s'",
+ (stringptr->len > 0) ? " " : "",
+ opt->option->defname,
+ strVal(opt->option->arg));
+ }
+
+ return stringptr->data;
+ }
+
+
+ /*
+ * Helper function for obtaining description of the option type.
+ */
+ static const char *
+ optclass_name(GenericOptionFlags opttype)
+ {
+ switch (opttype)
+ {
+ case FdwOpt:
+ return "foreign data wrapper";
+ case ServerOpt:
+ return "server";
+ case UserMappingOpt:
+ return "user mapping";
+ default:
+ return "unknown";
+ }
+
+ /* not reached */
+ return NULL;
+ }
+
/*
! * list_to_tuplestore - Helper function to convert a list to
* tuplestore usable in SRF.
+ *
+ * All the list elements are assumed to nodes of type "type".
*/
static void
! list_to_tuplestore(ReturnSetInfo *rsinfo, List *options, NodeTag type)
{
ListCell *cell;
TupleDesc tupdesc;
Tuplestorestate *tupstore;
! Datum values[3];
! bool nulls[3] = { 0 };
MemoryContext per_query_ctx;
MemoryContext oldcontext;
***************
*** 360,369 **** deflist_to_tuplestore(ReturnSetInfo *rsinfo, List *options)
foreach (cell, options)
{
! DefElem *def = lfirst(cell);
- values[0] = CStringGetTextDatum(def->defname);
- values[1] = CStringGetTextDatum(((Value *)def->arg)->val.str);
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
--- 469,502 ----
foreach (cell, options)
{
! NodeTag nodeType = ((Node *)lfirst(cell))->type;
!
! Assert(nodeType == type);
!
! if (nodeType == T_DefElem)
! {
! /* Simple key/value list */
! DefElem *def = lfirst(cell);
!
! values[0] = CStringGetTextDatum(def->defname);
! values[1] = CStringGetTextDatum(strVal(def->arg));
! }
! else if (nodeType == T_ForeignConnectionOption)
! {
! /* Foreign connection option list - class, key, value */
! ForeignConnectionOption *opt = lfirst(cell);
!
! values[0] = CStringGetTextDatum(optclass_name(opt->opttype));
! values[1] = CStringGetTextDatum(opt->option->defname);
! values[2] = CStringGetTextDatum(strVal(opt->option->arg));
! }
! else
! {
! ereport(ERROR,
! (errcode(ERRCODE_INTERNAL_ERROR),
! errmsg("unrecognized node type %d in list_to_tuplestore", type)));
! }
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
***************
*** 383,389 **** pg_options_to_table(PG_FUNCTION_ARGS)
{
Datum array = PG_GETARG_DATUM(0);
! deflist_to_tuplestore((ReturnSetInfo *) fcinfo->resultinfo, untransformRelOptions(array));
return (Datum) 0;
}
--- 516,562 ----
{
Datum array = PG_GETARG_DATUM(0);
! list_to_tuplestore((ReturnSetInfo *) fcinfo->resultinfo, untransformRelOptions(array), T_DefElem);
!
! return (Datum) 0;
! }
!
!
! /*
! * Return the libpq connection string for specified server and user.
! */
! Datum
! pg_foreign_connection_string(PG_FUNCTION_ARGS)
! {
! Name servername = PG_GETARG_NAME(0);
! Name username = PG_GETARG_NAME(1);
! Oid serverid;
! Oid userid;
!
! userid = get_roleid_checked(NameStr(*username));
! serverid = GetForeignServerOidByName(NameStr(*servername), false);
!
! return CStringGetTextDatum(GetForeignConnectionString(serverid, userid));
! }
!
!
! /*
! * Return the connection options for specified server and user.
! */
! Datum
! pg_foreign_connection_options(PG_FUNCTION_ARGS)
! {
! Name servername = PG_GETARG_NAME(0);
! Name username = PG_GETARG_NAME(1);
! Oid serverid;
! Oid userid;
! List *options;
!
! userid = get_roleid_checked(NameStr(*username));
! serverid = GetForeignServerOidByName(NameStr(*servername), false);
! options = GetForeignConnectionOptions(serverid, userid);
!
! list_to_tuplestore((ReturnSetInfo *) fcinfo->resultinfo, options, T_ForeignConnectionOption);
return (Datum) 0;
}
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2319,2324 **** DESCR("list of SQL keywords");
--- 2319,2330 ----
DATA(insert OID = 2289 ( pg_options_to_table PGNSP PGUID 12 1 3 0 f f f t t s 1 0 2249 "1009" "{1009,25,25}" "{i,o,o}" "{options_array,option_name,option_value}" _null_ pg_options_to_table _null_ _null_ _null_ ));
DESCR("convert generic options array to name/value table");
+ DATA(insert OID = 2316 ( pg_foreign_connection_string PGNSP PGUID 12 1 0 0 f f f t f s 2 1 25 "19 19" _null_ _null_ "{servername,username}" "({FUNCEXPR :funcid 745 :funcresulttype 19 :funcretset false :funcformat 0 :args <> :location 33})" pg_foreign_connection_string _null_ _null_ _null_ ));
+ DESCR("returns the libpq conninfo string for the server and username");
+
+ DATA(insert OID = 2319 ( pg_foreign_connection_options PGNSP PGUID 12 1 5 0 f f f t t s 2 1 2249 "19 19" "{19,19,25,25,25}" "{i,i,o,o,o}" "{servername,username,option_class,option_name,option_value}" "({FUNCEXPR :funcid 745 :funcresulttype 19 :funcretset false :funcformat 0 :args <> :location 33})" pg_foreign_connection_options _null_ _null_ _null_ ));
+ DESCR("returns the foreign connection options for the server and username");
+
DATA(insert OID = 1619 ( pg_typeof PGNSP PGUID 12 1 0 0 f f f f f s 1 0 2206 "2276" _null_ _null_ _null_ _null_ pg_typeof _null_ _null_ _null_ ));
DESCR("returns the type of the argument");
*** a/src/include/foreign/foreign.h
--- b/src/include/foreign/foreign.h
***************
*** 65,70 **** typedef struct UserMapping
--- 65,78 ----
} UserMapping;
+ typedef struct ForeignConnectionOption
+ {
+ NodeTag type; /* Node type */
+ GenericOptionFlags opttype; /* Option type, used as integer value, not flags */
+ DefElem *option; /* Option name and value */
+ } ForeignConnectionOption;
+
+
/*
* Foreign-data wrapper library function types.
*/
***************
*** 93,98 **** extern ForeignDataWrapper *GetForeignDataWrapperByName(const char *name,
--- 101,108 ----
bool missing_ok);
extern Oid GetForeignDataWrapperOidByName(const char *name, bool missing_ok);
extern ForeignDataWrapperLibrary *GetForeignDataWrapperLibrary(const char *libname);
+ extern List *GetForeignConnectionOptions(Oid serverid, Oid userid);
+ extern char *GetForeignConnectionString(Oid serverid, Oid userid);
/* Foreign data wrapper interface functions */
extern void _pg_validateOptionList(ForeignDataWrapper *fdw,
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
***************
*** 390,396 **** typedef enum NodeTag
T_TriggerData = 950, /* in commands/trigger.h */
T_ReturnSetInfo, /* in nodes/execnodes.h */
T_WindowObjectData, /* private in nodeWindowAgg.c */
! T_TIDBitmap /* in nodes/tidbitmap.h */
} NodeTag;
/*
--- 390,397 ----
T_TriggerData = 950, /* in commands/trigger.h */
T_ReturnSetInfo, /* in nodes/execnodes.h */
T_WindowObjectData, /* private in nodeWindowAgg.c */
! T_TIDBitmap, /* in nodes/tidbitmap.h */
! T_ForeignConnectionOption /* private in foreign/foreign.h */
} NodeTag;
/*
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
