13

I am writing a script to alter all functions of postgres(changing owner of each function). I am able to list down all the function names using postgres query but not able to list parameters for each of those functions.

My problem will be resolved if I get solution for any of the below mentioned problems:

  1. Is there any way to list down parameters' data type in each of the function.
  2. Do we have any approach to alter functions where instead of passing parameter type can I send some wild card. For Example can I write ALTER FUNCTION schemaname.func(text) OWNER TO 'newowner' as : ALTER FUNCTION schemaname.func(*) OWNER TO 'newowner'.
user1728310
  • 687
  • 2
  • 5
  • 14

2 Answers2

24

Is there any way to list down parameters' data type in each of the function.

Yes, use the pg_get_function_identity_arguments() function:

The following will create a SQL script to alter all functions from the someschema schema:

select 'alter function '||nsp.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to newowner;'
from pg_proc p
  join pg_namespace nsp ON p.pronamespace = nsp.oid
where nsp.nspname = 'someschema';

You can spool the output of that into a file and then run that generated script.

If you have function names that would require quoting, you probably need to use quote_ident to concatenate the function names.

You can wrap all that into a function and use dynamic SQL to make life easier if you need this on a regular basis.

  • That particular syntax didn't work for me (PostgreSQL 9.4.8, `ERROR: syntax error at or near "'newowner'"`). I had to remove the quoting around the `''newowner''`, ie `... owner to newowner;'` – James Brown Aug 12 '16 at 13:46
1

I had the same problem but pg_get_function_identity_arguments(p.oid) didn't work for me. So I replaced that function with oidvectortypes(p.proargtypes).

select 'alter function '||nsp.nspname||'.'||p.proname||'('||oidvectortypes(p.proargtypes)||') owner to newowner;'
from pg_proc p 
join pg_namespace nsp ON p.pronamespace = nsp.oid
where nsp.nspname = 'someschema';
Stefano Perduno
  • 11
  • 2