Обсуждение: Generating GRANT/REVOKE on functions from catalog
I am attempting to script the generation of grant/revoke statements for a database, and I'm having some trouble when it comes to functions. consider the following function: create function add(a integer, b integer) returns integer as $$ select $1 + $2; $$ language SQL; The statement I need to generate is: revoke all on function public.add(integer, integer) from someuser; I'm attempting to use the pg_proc table in the system catalogs, and I'm good up to the point where I need the parameter types. Can anyone give me a hand with this? Thanks, -- ------------------------------------------------------------------------ *Doug Gorley* | doug.gorley@gmail.com <mailto:doug.gorley@gmail.com>
Doug Gorley <doug.gorley@gmail.com> writes: > The statement I need to generate is: > revoke all on function public.add(integer, integer) from someuser; > I'm attempting to use the pg_proc table in the system catalogs, and I'm > good up to the point where I need the parameter types. Can anyone give > me a hand with this? Personally, I'd cast the function OID to regprocedure, instead of doing it the hard way ... regards, tom lane
That looks like exactly what I want. Is there an easy way to cast that to a string so that I can concatenate it into a GRANT statement? ------------------------------------------------------------------------ *Doug Gorley* | doug.gorley@gmail.com <mailto:doug.gorley@gmail.com> Tom Lane wrote: > Doug Gorley <doug.gorley@gmail.com> writes: > >> The statement I need to generate is: >> > > >> revoke all on function public.add(integer, integer) from someuser; >> > > >> I'm attempting to use the pg_proc table in the system catalogs, and I'm >> good up to the point where I need the parameter types. Can anyone give >> me a hand with this? >> > > Personally, I'd cast the function OID to regprocedure, instead of > doing it the hard way ... > > regards, tom lane >
Doug Gorley <doug.gorley@gmail.com> writes: > That looks like exactly what I want. Is there an easy way to cast that > to a string so that I can concatenate it into a GRANT statement? Well, since 8.3 you just cast it to a string ;-) In older versions I'd suggest a plpgsql wrapper function. plpgsql has always been very lax about letting you assign anything to anything, so you can cast by assignment. regards, tom lane
Perfect, I'm using the following function: create or replace function fn_sig(p_oid oid) returns text as $$ begin return p_oid::regprocedure; end; $$ language plpgsql; In the following query: select pg_namespace.nspname || '.' || fn_sig(pg_proc.oid) from pg_proc inner join pg_namespace on pg_proc.pronamespace = pg_namespace.oid Thanks very much! ------------------------------------------------------------------------ *Doug Gorley* | doug.gorley@gmail.com <mailto:doug.gorley@gmail.com> Tom Lane wrote: > Doug Gorley <doug.gorley@gmail.com> writes: > >> That looks like exactly what I want. Is there an easy way to cast that >> to a string so that I can concatenate it into a GRANT statement? >> > > Well, since 8.3 you just cast it to a string ;-) > > In older versions I'd suggest a plpgsql wrapper function. plpgsql has > always been very lax about letting you assign anything to anything, > so you can cast by assignment. > > regards, tom lane >
Doug Gorley <doug.gorley@gmail.com> writes: > Perfect, I'm using the following function: > create or replace function fn_sig(p_oid oid) returns text > as $$ > begin > return p_oid::regprocedure; > end; > $$ language plpgsql; > In the following query: > select > pg_namespace.nspname || > '.' || > fn_sig(pg_proc.oid) > from I wouldn't do that if I were you: regprocedure will already schema-qualify the function name if it's needed. The additional qualification you're trying to force will just result in syntax errors. regards, tom lane