Dynamic script to grant all tables and functions for a schema to another user or role

-- script will generate grant statement for all tables 

select 'grant all on table '||schemaname||'.'||tablename||' to <grantee>;' from pg_tables where schemaname in ('schemaname') order by schemaname, tablename;

-- script will generate grant statement for all functions 

select 'grant execute on function '||fschema||'.'||fname||'('||param||')'||' to <grantee>;'

from

(

select distinct fschema,fowner,fname,

--flanguage,proisagg,proargnames,proacl,

array_to_string(array_agg(t.typname) over (partition by fschema,fname),',') as param

from

(

select

proname as fname,nspname as fschema, a.rolname as fowner, l.lanname as flanguage, proisagg, proargnames,proacl,

unnest(proargtypes) as coltype

from pg_proc p 

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace

LEFT JOIN pg_catalog.pg_authid a on a.oid=p.proowner

LEFT JOIN pg_catalog.pg_language l on l.oid=p.prolang

where nspname like 'schemaname'

) as a LEFT OUTER JOIN pg_catalog.pg_type t on t.oid=a.coltype

order by fschema,fowner,fname) as b