pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting 'full' names of functions?
10+ messages / 5 participants
[nested] [flat]
* getting 'full' names of functions?
@ 2009-03-02 12:20 Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Re: getting 'full' names of functions? Ashish Karalkar <ashishka@synechron.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jan-Peter Seifert @ 2009-03-02 12:20 UTC (permalink / raw)
To: pgsql-admin
Hello,
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 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting 'full' names of functions?
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
@ 2009-03-02 13:51 ` Ashish Karalkar <ashishka@synechron.com>
2009-03-02 15:07 ` Re: getting 'full' names of functions? Tom Lane <tgl@sss.pgh.pa.us>
2009-03-04 17:38 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
0 siblings, 2 replies; 10+ messages in thread
From: Ashish Karalkar @ 2009-03-02 13:51 UTC (permalink / raw)
To: Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>; +Cc: pgsql-admin
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) of the 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting 'full' names of functions?
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Re: getting 'full' names of functions? Ashish Karalkar <ashishka@synechron.com>
@ 2009-03-02 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-03-04 14:41 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2009-03-02 15:07 UTC (permalink / raw)
To: Ashish Karalkar <ashishka@synechron.com>; +Cc: Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting 'full' names of functions?
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Re: getting 'full' names of functions? Ashish Karalkar <ashishka@synechron.com>
2009-03-02 15:07 ` Re: getting 'full' names of functions? Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-03-04 14:41 ` Jan-Peter.Seifert@gmx.de
2009-03-05 00:32 ` Re: getting 'full' names of functions? raf <raf@raf.org>
0 siblings, 1 reply; 10+ messages in thread
From: Jan-Peter.Seifert@gmx.de @ 2009-03-04 14:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; ashishka@synechron.com; +Cc: pgsql-admin
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 possibility 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.
Peter
--
Computer Bild Tarifsieger! GMX FreeDSL - Telefonanschluss + DSL
für nur 17,95 ¿/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting 'full' names of functions?
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Re: getting 'full' names of functions? Ashish Karalkar <ashishka@synechron.com>
2009-03-02 15:07 ` Re: getting 'full' names of functions? Tom Lane <tgl@sss.pgh.pa.us>
2009-03-04 14:41 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
@ 2009-03-05 00:32 ` raf <raf@raf.org>
2009-03-05 16:41 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
0 siblings, 1 reply; 10+ messages in thread
From: raf @ 2009-03-05 00:32 UTC (permalink / raw)
To: pgsql-admin
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. This possibility 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting 'full' names of functions?
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Re: getting 'full' names of functions? Ashish Karalkar <ashishka@synechron.com>
2009-03-02 15:07 ` Re: getting 'full' names of functions? Tom Lane <tgl@sss.pgh.pa.us>
2009-03-04 14:41 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
2009-03-05 00:32 ` Re: getting 'full' names of functions? raf <raf@raf.org>
@ 2009-03-05 16:41 ` Jan-Peter.Seifert@gmx.de
2009-03-05 18:45 ` Re: getting 'full' names of functions? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Jan-Peter.Seifert@gmx.de @ 2009-03-05 16:41 UTC (permalink / raw)
To: raf <raf@raf.org>; pgsql-admin
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? For now 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.pronamespace WHERE 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting 'full' names of functions?
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Re: getting 'full' names of functions? Ashish Karalkar <ashishka@synechron.com>
2009-03-02 15:07 ` Re: getting 'full' names of functions? Tom Lane <tgl@sss.pgh.pa.us>
2009-03-04 14:41 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
2009-03-05 00:32 ` Re: getting 'full' names of functions? raf <raf@raf.org>
2009-03-05 16:41 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
@ 2009-03-05 18:45 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2009-03-05 18:45 UTC (permalink / raw)
To: Jan-Peter.Seifert@gmx.de; +Cc: raf <raf@raf.org>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting 'full' names of functions?
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Re: getting 'full' names of functions? Ashish Karalkar <ashishka@synechron.com>
@ 2009-03-04 17:38 ` Jan-Peter.Seifert@gmx.de
2009-03-04 20:38 ` Re: getting 'full' names of functions? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Jan-Peter.Seifert@gmx.de @ 2009-03-04 17:38 UTC (permalink / raw)
To: Ashish Karalkar <ashishka@synechron.com>; +Cc: pgsql-admin
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_namespace n 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting 'full' names of functions?
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Re: getting 'full' names of functions? Ashish Karalkar <ashishka@synechron.com>
2009-03-04 17:38 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
@ 2009-03-04 20:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-03-05 16:23 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2009-03-04 20:38 UTC (permalink / raw)
To: Jan-Peter.Seifert@gmx.de; +Cc: Ashish Karalkar <ashishka@synechron.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: getting 'full' names of functions?
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Re: getting 'full' names of functions? Ashish Karalkar <ashishka@synechron.com>
2009-03-04 17:38 ` Re: getting 'full' names of functions? Jan-Peter.Seifert@gmx.de
2009-03-04 20:38 ` Re: getting 'full' names of functions? Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-03-05 16:23 ` Jan-Peter.Seifert@gmx.de
0 siblings, 0 replies; 10+ messages in thread
From: Jan-Peter.Seifert@gmx.de @ 2009-03-05 16:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin; ashishka@synechron.com
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 versions are 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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2009-03-05 18:45 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-02 12:20 getting 'full' names of functions? Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
2009-03-02 13:51 ` Ashish Karalkar <ashishka@synechron.com>
2009-03-02 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-03-04 14:41 ` Jan-Peter.Seifert@gmx.de
2009-03-05 00:32 ` raf <raf@raf.org>
2009-03-05 16:41 ` Jan-Peter.Seifert@gmx.de
2009-03-05 18:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-03-04 17:38 ` Jan-Peter.Seifert@gmx.de
2009-03-04 20:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-03-05 16:23 ` Jan-Peter.Seifert@gmx.de
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox