Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER

On Tue, Jun 18, 2024 at 3:33 PM PABLO ANDRES IBARRA DUPRAT <
Pablo(dot)Ibarra(at)itau(dot)cl> wrote:

> Hi Dear Community.
>
>
>
> I need your help with
> advices about the way to script a SQL command to generate a list of ALTER
> PROCEDURE and change owner of a big number of procedures.
>
> As you know to identify the
> procedure or function is neccesary to add to the name of routine and list
> of parameters with their data type in each case.
>
> Please any advice Will be
> appreciate.
>

This isn't perfect, because of the curly braces, but it's a start.
select format('ALTER PROCEDURE %s (%s) OWNER TO foo;',
pronamespace::regnamespace||'.'||proname
, proargnames)
from pg_proc
where pronamespace::regnamespace = 'some_schema';

Once the query returns the proper commands, execute it by replacing the
terminating ";" with "\gexec".

>