Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER

On Tue, Jun 18, 2024 at 1:29 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> 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".
>

Should use %I whenever possible (and %L)

... PROCEDURE %I.%I ...

David J.