Thread: getting oid of function

getting oid of function

From
Sibtay Abbas
Date:
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


Re: getting oid of function

From
Michael Fuhr
Date:
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/


Re: getting oid of function

From
Sibtay Abbas
Date:
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/
>


Re: getting oid of function

From
Michael Fuhr
Date:
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/


Re: getting oid of function

From
Neil Conway
Date:
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




Re: getting oid of function

From
Sibtay Abbas
Date:
>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
> 
>