Thread: getting oid of function
Hello all Is it possible to get the oid of a function on the basis of its name?. The scenario which i am currently facing is that i have the function name, now i want search the pg_proc system catalog on the basis of the function name and retrieve its Oid. Another confusion which i am facing is that, I am not sure whether Oid of a function is entered in pg_proc system catalog or not. Because i am not able to identify any relevant field. thank you
On Mon, Feb 14, 2005 at 12:47:44PM +0500, Sibtay Abbas wrote: > > Is it possible to get the oid of a function on the basis of its name?. One way is to cast the function name to regproc (or, with arguments, to regprocedure) and then to oid: SELECT 'atan'::regproc::oid; SELECT 'length(text)'::regprocedure::oid; See "Object Identifier Types" in the documentation for more info: http://www.postgresql.org/docs/8.0/static/datatype-oid.html > The scenario which i am currently facing is that i have the function name, now > i want search the pg_proc system catalog on the basis of the function > name and retrieve its Oid. SELECT oid FROM pg_proc WHERE proname = 'funcname'; A function can have multiple records in pg_proc if it can take different types and/or numbers of arguments, so you might have to allow for that. > Another confusion which i am facing is that, I am not sure whether Oid > of a function is entered in pg_proc system catalog or not. Because i > am not able to identify any relevant field. oid is a system column; tools that describe tables usually don't show system columns. You can query pg_attribute to see all of a table's columns. http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
thank you for the detailed reply But what i wanted to know is that how can we actually get a function's oid from its name from within postgresql code itself Actually i ve introduced a new statement in plpgsql, that supports calling a plpgsql function from within a plpgsql function for example CALL function_name( <params>); Here i only have the function name, i need to fetch the Oid of the function so that i can construct FmgrInfo and FunctionCallInfoData structures, so that i can compile (if required) and execute that function. There is a function like SearchSysCache(PROCOID,..................... that would retrieve the pg_proc Tuple on the basis of function Oid passed to it, so can use this function to return a pg_proc Tuple on the basis of a function name? Or is there any alternate function avaible that can achieve this Thank you regards Sibtay In case i am taking a wrong approach I would be thankful if anyone can correct me. On Mon, 14 Feb 2005 01:14:07 -0700, Michael Fuhr <mike@fuhr.org> wrote: > On Mon, Feb 14, 2005 at 12:47:44PM +0500, Sibtay Abbas wrote: > > > > Is it possible to get the oid of a function on the basis of its name?. > > One way is to cast the function name to regproc (or, with arguments, > to regprocedure) and then to oid: > > SELECT 'atan'::regproc::oid; > SELECT 'length(text)'::regprocedure::oid; > > See "Object Identifier Types" in the documentation for more info: > > http://www.postgresql.org/docs/8.0/static/datatype-oid.html > > > The scenario which i am currently facing is that i have the function name, now > > i want search the pg_proc system catalog on the basis of the function > > name and retrieve its Oid. > > SELECT oid FROM pg_proc WHERE proname = 'funcname'; > > A function can have multiple records in pg_proc if it can take > different types and/or numbers of arguments, so you might have to > allow for that. > > > Another confusion which i am facing is that, I am not sure whether Oid > > of a function is entered in pg_proc system catalog or not. Because i > > am not able to identify any relevant field. > > oid is a system column; tools that describe tables usually don't > show system columns. You can query pg_attribute to see all of a > table's columns. > > http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html > http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ >
On Mon, Feb 14, 2005 at 05:02:19PM +0500, Sibtay Abbas wrote: > > Actually i ve introduced a new statement in plpgsql, that supports calling > a plpgsql function from within a plpgsql function for example > > CALL function_name( <params>); How would this differ from PERFORM? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Mon, 2005-02-14 at 17:02 +0500, Sibtay Abbas wrote: > thank you for the detailed reply > But what i wanted to know is that how can we actually get a function's > oid from its > name from within postgresql code itself You'll want to query the syscache. Note that due to function overloading, there may be multiple functions with the same name, so you'll need to figure out which one ought to be invoked by using the number and types of the parameters. See FuncnameGetCandidates() in namespace.c for an example. -Neil
>How would this differ from PERFORM? I think perform goes through the SQL by using SPI to execute the function, where as this statement will invoke a plpgsql function without going through the sql ( :-) ..in case i manage to add this statement ) thankz alot for your replies regards Sibtay On Tue, 15 Feb 2005 14:55:38 +1100, Neil Conway <neilc@samurai.com> wrote: > On Mon, 2005-02-14 at 17:02 +0500, Sibtay Abbas wrote: > > thank you for the detailed reply > > But what i wanted to know is that how can we actually get a function's > > oid from its > > name from within postgresql code itself > > You'll want to query the syscache. Note that due to function > overloading, there may be multiple functions with the same name, so > you'll need to figure out which one ought to be invoked by using the > number and types of the parameters. See FuncnameGetCandidates() in > namespace.c for an example. > > -Neil > >