How to get the list of functions in database in PostgreSQL along with its parameters?

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>;

I have seen that post, but there I am not able to get the list of my arguments that I have for my functions. — Yousuf Sultan
– Yousuf Sultan, Commented Jun 4, 2014 at 9:49

Craig Ringer · Accepted Answer · 2014-06-04 10:38:19Z

8

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.

edited Jun 4, 2014 at 10:38

answered Jun 4, 2014 at 10:05

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yousuf Sultan Over a year ago

Thanks a lot Craig. This has been of great help for me. :)

klin Over a year ago

GRANT ... ON ALL FUNCTIONS! Sweet.

klin · Accepted Answer · 2014-06-04 10:08:15Z

3

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 $$;

answered Jun 4, 2014 at 10:08

klin

123k15 gold badges240 silver badges262 bronze badges

2 Comments

Craig Ringer Over a year ago

Ah, yes, that's the better thing to use over oidvectortypes.

Yousuf Sultan Over a year ago

This too is a nice option!

Collectives™ on Stack Overflow

How to get the list of functions in database in PostgreSQL along with its parameters?

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related