Thread: getting 'full' names of functions?
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
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
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
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
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
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
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
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
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
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