postgresql 遍历参数,如何在PostgreSQL中获取数据库中的函数列表及其参数？

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 .() TO ;

解决方案

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.