Обсуждение: Dynamic function execution?
Can anyone provide me with some direction on how to write a function I can load into postgres that will execute a function specified by OID (or regproc/regprocedure) at runtime, with type safety? I've been able to write such a function in C, but I was unable to figure out how to determine the parameters that the specified function expects, so I can prevent calling a function that doesn't match the expected signature (which segfaults postgres). -Nick Johnson
On Mon, Mar 13, 2006 at 10:45:47PM -0800, Nick Johnson wrote: > Can anyone provide me with some direction on how to write a function > I can load into postgres that will execute a function specified by > OID (or regproc/regprocedure) at runtime, with type safety? I've been > able to write such a function in C, but I was unable to figure out > how to determine the parameters that the specified function expects, > so I can prevent calling a function that doesn't match the expected > signature (which segfaults postgres). Does the calling function have to be written in C? In PL/pgSQL you could easily query pg_proc with the oid to get the called function's name, argument types, etc., then build an appropriate string to EXECUTE. In C you could use SearchSysCache() and SysCacheGetAttr(); search through the source code for examples of calls to those functions with a first argument of PROCOID. Why do you need to do this? What problem are you trying to solve? -- Michael Fuhr
On 14/03/2006, at 12:05 AM, Michael Fuhr wrote: > On Mon, Mar 13, 2006 at 10:45:47PM -0800, Nick Johnson wrote: > >> Can anyone provide me with some direction on how to write a function >> I can load into postgres that will execute a function specified by >> OID (or regproc/regprocedure) at runtime, with type safety? I've been >> able to write such a function in C, but I was unable to figure out >> how to determine the parameters that the specified function expects, >> so I can prevent calling a function that doesn't match the expected >> signature (which segfaults postgres). >> > > Does the calling function have to be written in C? In PL/pgSQL you > could easily query pg_proc with the oid to get the called function's > name, argument types, etc., then build an appropriate string to > EXECUTE. I considered this, but I'd rather not do it by string manipulation and dynamic SQL - it seems a kludge. > In C you could use SearchSysCache() and SysCacheGetAttr(); > search through the source code for examples of calls to those > functions with a first argument of PROCOID. Thanks for the tips. > > Why do you need to do this? What problem are you trying to solve? > I want to associate Postgres functions with rows of a table (eg, a table column of datatype regproc or regprocedure) and be able to execute the function associated with that row in a query. -Nick Johnson
On Tue, Mar 14, 2006 at 07:21:51AM -0800, Nick Johnson wrote: > On 14/03/2006, at 12:05 AM, Michael Fuhr wrote: > >Why do you need to do this? What problem are you trying to solve? > > I want to associate Postgres functions with rows of a table (eg, a > table column of datatype regproc or regprocedure) and be able to > execute the function associated with that row in a query. Could you post an example? Others might be interested in seeing an application of something like that. -- Michael Fuhr
On 14/03/2006, at 10:26 AM, Michael Fuhr wrote:
On Tue, Mar 14, 2006 at 07:21:51AM -0800, Nick Johnson wrote:On 14/03/2006, at 12:05 AM, Michael Fuhr wrote:Why do you need to do this? What problem are you trying to solve?I want to associate Postgres functions with rows of a table (eg, atable column of datatype regproc or regprocedure) and be able toexecute the function associated with that row in a query.Could you post an example? Others might be interested in seeingan application of something like that.
The example that's actually driving this is rather stupid (though amusing). I'm building a Postgres based adventure game. Eg, SELECT n(); SELECT look(); etc. I want to be able to execute on_enter, on_exit etc events, the handles to the event functions being stored in the table.
A more sensible example (though not one this will be immediately used for) is something we're doing at my work: The database contains a number of stored procedures implementing 'models' - they're functions that essentially perform data-mining on the database. There's a table that has metadata about each of these models, allowing the system to determine which ones need executing, and when. Being able to directly call them would be useful here, too.
-Nick Johnson
On 3/14/06, Michael Fuhr <mike@fuhr.org> wrote: > On Tue, Mar 14, 2006 at 07:21:51AM -0800, Nick Johnson wrote: > > On 14/03/2006, at 12:05 AM, Michael Fuhr wrote: > > >Why do you need to do this? What problem are you trying to solve? > > > > I want to associate Postgres functions with rows of a table (eg, a > > table column of datatype regproc or regprocedure) and be able to > > execute the function associated with that row in a query. > > Could you post an example? Others might be interested in seeing > an application of something like that. If you go a ways back on the performance list you can see an example. It's very simple: there are C functions which you can call which take the oid and parameters. Then you can do clever things like do callbacks in plpgsql a function taking the function argument as a string and looking up its oid. to the original poster...it's probably possible. one way would be to sanity check pg_proc on the C side (at the least, check the # args). there might be better/faster ways though Merlin
On 14/03/2006, at 6:52 PM, Merlin Moncure wrote:
to the original poster...it's probably possible. one way would be tosanity check pg_proc on the C side (at the least, check the # args).there might be better/faster ways though
I just finished an implementation based on the suggestions of Michael Fuhr. My function retrieves system cache records for itself and the function it's being asked to call, then ensures the return types and all the argument types match. If that test is passed, it modifies its own fcinfo struct into the one required to call the function the user really wants, then calls FunctionCallInvoke to call it.
With this method, on the PGSQL end, you can CREATE FUNCTION with any arbitrary parameters and return type as long as the first parameter is oid, regproc, or regprocedure, and then call any function with a matching signature using the defined function.
Still not done (and won't be done, unless I develop a need or someone else wants it ) is determining when function calls are compatible even though they're not identical (eg, through use of polymorphic functions and ANYELEMENT/ANYARRAY).
-Nick Johnson