How to get the list of functions in database in Po

Question

I have a database which has over 1000 functions. I want to GRANT execution permission to certain users. I can't do it manually, therefore I want to get a list of functions and write a code to generate the GRANT EXECUTE script for all the functions. I tried the following script but this is not giving me the parameters. I am unable to get the parameters with this query.

SELECT  'GRANT EXECUTE ON FUNCTION '||nspname||'.'||proname||' TO gis;'
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE   nspname = 'ccdb'

How can I get my desired result with the datatypes?

Like,

GRANT EXECUTE ON FUNCTION <schema_name>.<table_name>(<list of arguments>) TO <user_name>;

Answer 1

There's a handy function to help you out: oidvectortypes.

SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) 
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid);

shows all functions with arguments. You can adapt that format incantation to generate any desired statements, and if you like, LOOP over it to feed the generated statement into EXECUTE in PL/PgSQL.

Credit to Leo Hsu and Regina Obe at Postgres Online for pointing out oidvectortypes. I wrote similar functions before, but used complex nested expressions that this function gets rid of the need for.

Note that in this case you don't have to do any custom SQL generation at all, though. Just use GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema TO ... if you're on a vaguely recent PostgreSQL.

Answer 2

You should use pg_get_function_arguments(func_oid). Dynamic GRANT must be executed in anonymous code block or function (change my_schema and my_username to actual values):

do $$
declare
    funcdef text;
begin
    for funcdef in
        select format('%s.%s (%s)', 
            nspname, proname, pg_get_function_arguments(p.oid))
        from pg_proc p
        join pg_namespace n on pronamespace = n.oid
        where nspname = 'my_schema' 
        and not proisagg
    loop
        execute format ('GRANT EXECUTE ON FUNCTION %s TO my_username', funcdef);
    end loop;
end $$;