How to alter owner of a function in postgres

Question

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:

Is there any way to list down parameters' data type in each of the function.
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'.

score 27 · Accepted Answer · 2017-02-28 15:37:05Z

27

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.

edited Feb 28, 2017 at 15:37

answered Apr 2, 2014 at 6:42

user330315

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

1 Comment

James Brown Over a year ago

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

Stefano Perduno · Accepted Answer · 2015-07-09 10:55:28Z

2

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

answered Jul 9, 2015 at 10:55

Stefano Perduno

212 bronze badges

Collectives™ on Stack Overflow

How to alter owner of a function in postgres

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related