Thread: getting 'full' names of functions?

getting 'full' names of functions?

From
"Jan-Peter Seifert"
Date:
Hello,

before I reinvent the wheel I'd like to know whether there's a shortcut for getting the 'full' name (incl. argtypes) of
thefunctions within a database in order to REVOKE priviliges on them given to certain users. 
I don't see anything in the docs:
http://www.postgresql.org/docs/8.3/interactive/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE

Obviously you can't just use the specific_name from information_schema.routine_privileges for this.

Any help is appreciated.

Thank you very much,

Peter
--
Computer Bild Tarifsieger! GMX FreeDSL - Telefonanschluss + DSL
für nur 17,95 ¿/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a

Re: getting 'full' names of functions?

From
Ashish Karalkar
Date:
Jan-Peter Seifert wrote:
> Hello,
>
> before I reinvent the wheel I'd like to know whether there's a shortcut for getting the 'full' name (incl. argtypes)
ofthe functions within a database in order to REVOKE priviliges on them given to certain users. 
> I don't see anything in the docs:
http://www.postgresql.org/docs/8.3/interactive/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE
>
> Obviously you can't just use the specific_name from information_schema.routine_privileges for this.
>
> Any help is appreciated.
>
> Thank you very much,
>
> Peter
>
May be this will help you:


SELECT
n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)
||')'
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND pg_catalog.pg_function_is_visible(p.oid)
AND nspname != 'pg_catalog'


--Ashish

Re: getting 'full' names of functions?

From
Tom Lane
Date:
Ashish Karalkar <ashishka@synechron.com> writes:
> Jan-Peter Seifert wrote:
>> Obviously you can't just use the specific_name from information_schema.routine_privileges for this.

> May be this will help you:

Easier is just
    select oid::regprocedure from pg_proc where <whatever>

            regards, tom lane

Re: getting 'full' names of functions?

From
Jan-Peter.Seifert@gmx.de
Date:
Hello Ashish, Hello Tom,

thank you very much for your quick and helpful replies - I really appreciate that.

> > May be this will help you:
>
> Easier is just
>     select oid::regprocedure from pg_proc where <whatever>

I guess I'll go with the very nifty type cast suggested by Tom though as this covers aggregate functions as well. This
possibilityshould be mentioned in the docs - together with the system information functions
http://www.postgresql.org/docs/8.3/interactive/functions-info.html

Too bad that this function syntax isn't part of information_schema.

Thank you very much to both of you.

Peter
--
Computer Bild Tarifsieger! GMX FreeDSL - Telefonanschluss + DSL
für nur 17,95 ¿/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a

Re: getting 'full' names of functions?

From
Jan-Peter.Seifert@gmx.de
Date:
Hello Ashish, Hello Tom,

> > before I reinvent the wheel I'd like to know whether there's a shortcut
> for getting the 'full' name (incl. argtypes) of the functions within a
> database in order to REVOKE priviliges on them given to certain users.

I combined your suggestions into this query I'll be using for now:

SELECT DISTINCT n.nspname || '.' || p.oid::regprocedure::text FROM pg_catalog.pg_proc p LEFT JOIN
pg_catalog.pg_namespacen ON n.oid = p.pronamespace WHERE pg_catalog.pg_function_is_visible(p.oid) AND nspname !~*
'^pg_'AND nspname != 'information_schema'; 

Thank you very much again,

Peter
--
Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger01

Re: getting 'full' names of functions?

From
Tom Lane
Date:
Jan-Peter.Seifert@gmx.de writes:
> I combined your suggestions into this query I'll be using for now:

> SELECT DISTINCT n.nspname || '.' || p.oid::regprocedure::text FROM

This is flat *wrong*, as you'll soon find if you are working with
functions in more than one schema.  regprocedure already puts a
schema qualification on the name if one is needed.

            regards, tom lane

Re: getting 'full' names of functions?

From
raf
Date:
Jan-Peter.Seifert@gmx.de wrote:

> Hello Ashish, Hello Tom,
>
> thank you very much for your quick and helpful replies - I really appreciate that.
>
> > > May be this will help you:
> >
> > Easier is just
> >     select oid::regprocedure from pg_proc where <whatever>
>
> I guess I'll go with the very nifty type cast suggested by Tom though as this covers aggregate functions as well.
Thispossibility should be mentioned in the docs - together with the system information functions
http://www.postgresql.org/docs/8.3/interactive/functions-info.html
>
> Too bad that this function syntax isn't part of information_schema.
>
> Thank you very much to both of you.
>

note that this method doesn't produce a complete function
signature. the precision and scale of numerics are not
included in the output. hopefully, that won't matter for
your needs.

cheers,
raf


Re: getting 'full' names of functions?

From
Jan-Peter.Seifert@gmx.de
Date:
Hello raf,

> > > Easier is just
> > >     select oid::regprocedure from pg_proc where <whatever>

> note that this method doesn't produce a complete function
> signature. the precision and scale of numerics are not
> included in the output. hopefully, that won't matter for
> your needs.

Oh. So functions expecting e.g. numeric(5,2) as argument wouldn't be listed correctly? Is this going to be fixed then?
Fornow all I need is the 'full' function name necessary for GRANT/REVOKE. 

I guess this will do for now for 'non-numeric'-functions?:

SELECT DISTINCT p.oid::regprocedure::text FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
p.pronamespaceWHERE nspname !~* '^pg_' AND nspname != 'information_schema' 

Thank you very much,

Peter
--
Computer Bild Tarifsieger! GMX FreeDSL - Telefonanschluss + DSL
für nur 17,95 Euro/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a

Re: getting 'full' names of functions?

From
Jan-Peter.Seifert@gmx.de
Date:
Hello Tom,

> > I combined your suggestions into this query I'll be using for now:
>
> > SELECT DISTINCT n.nspname || '.' || p.oid::regprocedure::text FROM
>
> This is flat *wrong*, as you'll soon find if you are working with
> functions in more than one schema.  regprocedure already puts a
> schema qualification on the name if one is needed.

You are right. I was more concerned with getting the same number of functions as in pgAdmin III. There were two
'missing'because of pg_function_is_visible: 

public.plpgsql_call_handler()
public.plpgsql_validator(oid)

They are obviously relicts from an earlier PostgreSQL-version. Could they cause problems if left be although the new
versionsare now in pg_catalog which obviously is being searched before all other schemata? 

But why is regprocedure not just addding the schema to all of the functions then?

Thank you very much,

Peter
--
Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger01

Re: getting 'full' names of functions?

From
Tom Lane
Date:
Jan-Peter.Seifert@gmx.de writes:
> Hello raf,
>>> Easier is just
>>> select oid::regprocedure from pg_proc where <whatever>

>> note that this method doesn't produce a complete function
>> signature. the precision and scale of numerics are not
>> included in the output. hopefully, that won't matter for
>> your needs.

> Oh. So functions expecting e.g. numeric(5,2) as argument wouldn't be
listed correctly? Is this going to be fixed then?

No, because it's not broken.  Precision/scale of numerics aren't
relevant to function signatures.  A function can take or return
a numeric, full stop --- it doesn't matter what the precision is.
The same goes for other type modifiers such as varchar maxlength.

            regards, tom lane