RE: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER

Hi

I would this this is what you are looking for

It will change owner on Func's and Proc's where current owner is foo to be bar.



\set ECHO all
\set ON_ERROR_STOP on

DO
$proc$

declare
v_rec record;
v_sql text;
v_owner_to_find text;
v_owner_to_set text;

begin
v_owner_to_find := 'foo';
v_owner_to_set := 'bar';

for v_rec in (SELECT n.nspname,
                     case p.prokind when 'p' then 'procedure ' else 'function ' end as what_is_it,
                     p.proname,
                     pg_catalog.pg_get_function_identity_arguments(p.oid) proc_interface
              FROM pg_catalog.pg_proc p
              LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = p.pronamespace)
              WHERE
              p.prokind in ('p','f') and
              pg_catalog.pg_get_userbyid(p.proowner) = v_owner_to_find
              ORDER BY n.nspname, what_is_it, p.proname, proc_interface
             )
loop

  v_sql := format('alter %s %s.%s (%s) owner to %s;',v_rec.what_is_it,v_rec.nspname, v_rec.proname,
v_rec.proc_interface,v_owner_to_set);
  raise notice '%',v_sql;
  execute v_sql;

end loop;

end

$proc$
;

 
This message is only for the use of the persons(s) to whom it is intended. It may contain privileged and confidential
informationwithin the meaning of applicable law. If you are not the intended recipient, please do not use this
informationfor any purpose, destroy this message and inform the sender immediately. The views expressed in this
communicationmay not necessarily be the views held by Sony Music Entertainment 

-----Original Message-----
From: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
Sent: Tuesday, June 18, 2024 5:14 PM
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: RE: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER

[You don't often get email from pablo.ibarra@itau.cl. Learn why this is important at
https://aka.ms/LearnAboutSenderIdentification] 

EXTERNAL SENDER


Thanks in advance



-----Mensaje original-----
De: Tom Lane <tgl@sss.pgh.pa.us>
Enviado el: martes, 18 de junio de 2024 17:09
Para: David G. Johnston <david.g.johnston@gmail.com>
CC: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>; pgsql-admin@lists.postgresql.org
Asunto: Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Specifically:

> select id.*, pg_proc.*, tableoid from pg_proc,
> pg_identify_object(1255,oid,0) as id;

Personally, I'd cast the procedure's OID to regprocedure instead.
More or less the same output, doesn't require magic numbers.

(Although I think you could write "pg_proc.tableoid" instead of "1255", if you're intent on using pg_identify_object.)

                        regards, tom lane Para asegurar la adecuada lectura en todo tipo de correos electronicos, se
hanomitido intencionalmente los signos y acentos diacriticos del idioma castellano. La informacion contenida en este
mensajey cualquier archivo adjunto es confidencial y no puede ser usada por mas personas que sus destinatarios. El uso
noautorizado de esta informacion puede ser sancionado de conformidad con el Codigo Penal chileno. Si ha recibido este
correopor error, por favor notifique al remitente respondiendo este mismo mensaje y elimine el mensaje y todos los
archivosadjuntos. Internet no puede garantizar la integridad de este mensaje, por lo que el Banco no se hace
responsablesi el contenido del mismo ha sido alterado. 





This email originated from outside of Sony Music.  Do not click links or open attachments unless you recognize the
senderand know the content is safe.