如何在PostgreSQL中获取数据库中的函数列表及其参数？ [英] How to get the list of functions in database in PostgreSQL along with its parameters?

问题描述

我有一个拥有超过1000个函数的数据库。我想授予某些用户的执行权限。我不能手动执行，因此我想获取函数列表并编写代码为所有函数生成GRANT EXECUTE脚本。我尝试了下面的脚本，但这并没有给我参数。

  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'
  

如何用数据类型获得我想要的结果？

 授权执行功能< schema_name>。< table_name>（<参数列表>） TO< user_name>; 
  

解决方案

有一个方便的功能可以帮助您： oidvectortypes 。

 选择格式（'％I.％I（％s） '，ns.nspname，p.proname，oidvectortypes（p.proargtypes））
 FROM pg_proc p INNER JOIN pg_namespace ns ON（p.pronamespace = ns.oid）; 
  

显示所有带参数的函数。你可以修改格式咒语来产生任何想要的语句，如果你喜欢， LOOP 就可以生成语句转换为PL / PgSQL中的 EXECUTE 。 $ b

相信 Leo Hsu和Regina Obe在Postgres Online 指出 oidvectortypes 。我之前编写了类似的函数，但使用了复杂的嵌套表达式，该函数不需要这些函数。

请注意，在这种情况下，您不必尽管如此，仍然可以执行任何自定义SQL生成。 只需使用在SCHEMA myschema中对所有函数执行GRANT EXECUTE T​​O ... ，如果你在最近的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 <schema_name>.<table_name>(<list of arguments>) TO <user_name>;

解决方案

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.

这篇关于如何在PostgreSQL中获取数据库中的函数列表及其参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！