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 information within the meaning of applicable law. If you are not the intended recipient, please do not use this information for any purpose, destroy this message and inform the sender immediately. The views expressed in this communication may not necessarily be the views held by Sony Music Entertainment

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

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

EXTERNAL SENDER

Thanks in advance

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

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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 han omitido intencionalmente los signos y acentos diacriticos del idioma castellano. La informacion contenida en este mensaje y cualquier archivo adjunto es confidencial y no puede ser usada por mas personas que sus destinatarios. El uso no autorizado de esta informacion puede ser sancionado de conformidad con el Codigo Penal chileno. Si ha recibido este correo por error, por favor notifique al remitente respondiendo este mismo mensaje y elimine el mensaje y todos los archivos adjuntos. Internet no puede garantizar la integridad de este mensaje, por lo que el Banco no se hace responsable si 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 sender and know the content is safe.