Take the 2-minute tour ×

Modify OWNER on all tables simultaneously in Postgresql describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ignore the functions I created.


share|improve this question
add comment

migrated from stackoverflow.com Dec 24 '11 at 23:40

This question came from our site for professional and enthusiast programmers.

2 Answers



In short, try this instead:

            || quote_ident(n.nspname) || '.' 
            || quote_ident(p.proname) || '(' 
            || pg_catalog.pg_get_function_identity_arguments(p.oid)
            || ') OWNER TO foo;'
          , E'\n') AS _sql
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public';
-- AND p.proname ~~ 'f_%'


ALTER FUNCTION public.bar(text, text) OWNER TO foo;
ALTER FUNCTION public.foo(x integer) OWNER TO foo;



You could put all of this into a function like I demonstrate in this related answer.

share|improve this answer
add comment
up vote -1 down vote accepted



share|improve this answer
You are asking for proof that it will not break something, and my counterargument is that if you are downvoting something, you should include an explanation of what it will break and how/ why. If you cannot, then the answer is not wrong, misleading, unuseful, or unhelpful, which are the criteria for a downvote. The relationships in the metadata tables were not difficult to figure out in this case, upon a bit of examination, and as I said, it works swimmingly. The burden of proof should be on the downvoter; I expect you will have difficulties finding what this answer will break. –  Jeremy Holovacs Dec 28 '11 at 4:16
show 2 more comments

Your Answer


