postgres - alter owner of all functions in schema

Firstly this query will return the function name including argument definition and the schema it belongs to. The function name alone is not enough to perform an ALTER FUNCTION query due to the prossibilities of overloaded functions.

select proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')', n.nspname from pg_proc pr join pg_namespace n on pr.pronamespace = n.oid where n.nspname in ('public','myschema');

SQL to change the owner:

ALTER FUNCTION %s OWNER TO new_owner;


Below is an example of the above using a simple perl script:


$query = $dbh->prepare (qq{select proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')', n.nspname from pg_proc pr join pg_namespace n on pr.pronamespace = n.oid where n.nspname in ($namespaces);});
$query->execute ();

while (($Table, $Schema) = $query->fetchrow ()) {
$Command = sprintf "ALTER FUNCTION %s.%s OWNER TO test; \n", $Schema, $Table;
        $i = $dbh->do ($Command);
}