Generating GRANT/REVOKE on functions from catalog

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:

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:

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