From: "Vitale, Anthony, Sony Music" <anthony.vitale@sonymusic.com>
To: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>, Tom Lane
	<tgl@sss.pgh.pa.us>, "David G. Johnston" <david.g.johnston@gmail.com>
CC: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: RE: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
Thread-Topic: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
Thread-Index: AdrBtc8Wa3jKK7C+QuS+qp3e08m42QAAdLMAAALJdwAAADkTgAAAMUMgAAGQLdA=
Date: Tue, 18 Jun 2024 22:02:13 +0000
Message-ID: 
 <AS8PR02MB10078F267FD309FCDA816E79799CE2@AS8PR02MB10078.eurprd02.prod.outlook.com>
References: 
 <CPUP152MB401802939E39B07A3BD264A887CE2@CPUP152MB4018.LAMP152.PROD.OUTLOOK.COM>
 <CAKFQuwY0zN14+czWopnT3cZtK2vasrH08XCpXv_u84ejUU2u-g@mail.gmail.com>
 <CAKFQuwY4xtq=M6x2g2BLTgnjvgO=_7ORDF4gnuYX8w4J7eLLcA@mail.gmail.com>
 <4191919.1718744911@sss.pgh.pa.us>
 <CPUP152MB4018C1DFC4FA4E667DA2758A87CE2@CPUP152MB4018.LAMP152.PROD.OUTLOOK.COM>
In-Reply-To: 
 <CPUP152MB4018C1DFC4FA4E667DA2758A87CE2@CPUP152MB4018.LAMP152.PROD.OUTLOOK.COM>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?iso-8859-1?Q?XLFLy2LTV1AV76Pb4wSQrDZhqwjAFbZdEiwKr2j2nk+RkD0IJShPQo8G4n?=
 =?iso-8859-1?Q?BwGS1jbjDAoe7BrU5T/J3u84pEOquoTEorwDhs5UCvPbetwr8GfEAypHKe?=
 =?iso-8859-1?Q?VkqeUKzmfIv500EZfd/poCHJluaGI5XsWIRGQbd6PQGnK24KDi6rlPxUlV?=
 =?iso-8859-1?Q?Klku2Gp4FOVeKnNCXHHPoG9ySY5bIrEIX8KM9uf34wWKus2eykOAGd8gZ9?=
 =?iso-8859-1?Q?rt4wBKr4EeQ/0nji2liZgfb9Kp5vdIzFjYUE+KOPdVNnXbt9CahCcI6Znh?=
 =?iso-8859-1?Q?rVqc5nBtrUD6MNJfGpWscNBd5q8dwmThPeHcaAWJ7fqysy9xIcWUaNZBmy?=
 =?iso-8859-1?Q?C/C1zzozuw22dkMcqaLcbrMhT7KC3EbV6/nXHFdQiBbeJMw3ldny5OQJVa?=
 =?iso-8859-1?Q?oCgMPyghg0DOdXGQeKEXQv8wxjvfjZkxW4wAlssJ//syXYiZrpusyQaS9h?=
 =?iso-8859-1?Q?4lh2mvyx9xG+2aBl1+JbLci5VLYD+hDJjzjbSK0qADkvwTCrjyCBaf6Lan?=
 =?iso-8859-1?Q?FyeFlfiE6qNalnOuB1BcSHpye6jPPkEqRC4JaNY+mJbkCAu/tltV/EbsnT?=
 =?iso-8859-1?Q?UrYCV3r7Y3QshG2mVVQSVYaF5QG8gNqghRHTUd2k+Bd6Be/kuSNEZ3O9hk?=
 =?iso-8859-1?Q?7XNMLIDhOFXNLjWWqAg2uiw70Nrnk2Zqw3S0SmI9j6+db6grbKkvOBoxjm?=
 =?iso-8859-1?Q?LQ+ndtyRVnj9eQrZKw/AD4uWZ9yfzIPiHJSq6rdUkgnU5E8WxYU0zycKWq?=
 =?iso-8859-1?Q?A+p2CYemeHh2hu3GEu77kf69motMCA2ib6vwOGFVtidBBGhTkn/XeA2iiq?=
 =?iso-8859-1?Q?XChzTwOcao3E2u7rOdvYGKKYFBJYatyKDm8BpNU0HcPjS4agHI6m5clx2a?=
 =?iso-8859-1?Q?1zGi83ILjxMnocY5KNtWllIBk5q0i0q1XUYmhCBfFcEW2AsqtVJnGUfuFI?=
 =?iso-8859-1?Q?Xg8Q5cKjfCgTJq61TaMItPvv/85Z7Oajn8BBp01qHorERJ8nNpKgvkU3cV?=
 =?iso-8859-1?Q?XjooRt7dgHLkOgtRPubNlp+SsG58yGVJzB4lyo/we1y9WVGbEFuGH10sjb?=
 =?iso-8859-1?Q?BZXtID3CftQ2nOoSO5Mykj6COouE9m1x3jqWum5zAViAmTHAwQy2UDWxve?=
 =?iso-8859-1?Q?1T4XbbsfGK3Q4lcqJ8Nspf15DgsJbKiIKOYCsUarmDEFekBGRLafvC6MSv?=
 =?iso-8859-1?Q?Ve8rh7jAyOBzcm1FcpO/L8cOu/KC5osVfEDmzl/5thasD+8r3b04UnAMsB?=
 =?iso-8859-1?Q?htDUBFlSR1488ineXGhQwYTAxM5bOMghptUM0aOfXXokkZUQETHjfDnZLb?=
 =?iso-8859-1?Q?al1VeTCs4lfCyqEdGCjwdtmrLh9oI14ZXj/Oa90Hpkt5BHkBd+RSait6Sb?=
 =?iso-8859-1?Q?ToAmJHSX3LfU1Ct9ZjgRscezAHw1IkAGNYHBV43Zjg0RS8aSNDms+8P3Nw?=
 =?iso-8859-1?Q?lRgrY2H+7w6y6N3JQqexaVPWdbMHaxzQQBSlDHwGSs/0jVGiW5V/eJ7Cvt?=
 =?iso-8859-1?Q?h0TbyI4BKVjL9aU2qrQsr/b77X/8oqeOght2JB/Ef1kY6xM2LMDFtIp91L?=
 =?iso-8859-1?Q?Wn161qQGYDRufEhF9Y/W0n9fKrgMGJZvozxbOKac3n2WsG3Lf3Fkara5NS?=
 =?iso-8859-1?Q?EBtVSpS4HAUq0NPGay1dRLa3YCX6igD4KK6sLQ6edZVL0GjSiuLslRmQ?=
 =?iso-8859-1?Q?=3D=3D?=
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: sonymusic.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: AS8PR02MB10078.eurprd02.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 ec2f1af8-e6cd-4a71-b2db-08dc8fe24f89
X-MS-Exchange-CrossTenant-originalarrivaltime: 18 Jun 2024 22:02:13.9123
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: f0aff3b7-91a5-4aae-af71-c63e1dda2049
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 wSg/Cxb7YzE+U/ATpxMQ5noAM+QMSylMwr2N2tNxX6WwdclsSxibXqwVMZZqkORujE2OHTVdU5N6w4TG35IrCnnzvZmfa264ZHXwHed15CE=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DB8PR02MB5772
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/AS8PR02MB10078F267FD309FCDA816E79799CE2%40AS8PR02MB10078.eurprd02.prod.outlook.com>
Precedence: bulk

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 :=3D 'foo';
v_owner_to_set :=3D 'bar';

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

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

end loop;

end

$proc$
;

=A0
This message is only for the use of the persons(s) to whom it is intended. =
It may contain=A0privileged and confidential information within the meaning=
 of applicable law. If you are not the intended recipient, please do not us=
e this information for any purpose, destroy this message and inform the sen=
der immediately. The views expressed in this communication may not necessar=
ily be the views held by Sony Music Entertainment

-----Original Message-----
From: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>=20
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 imp=
ortant 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.po=
stgresql.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 han omitido intencionalmente los =
signos y acentos diacriticos del idioma castellano. La informacion contenid=
a en este mensaje y cualquier archivo adjunto es confidencial y no puede se=
r usada por mas personas que sus destinatarios. El uso no autorizado de est=
a informacion puede ser sancionado de conformidad con el Codigo Penal chile=
no. Si ha recibido este correo por error, por favor notifique al remitente =
respondiendo este mismo mensaje y elimine el mensaje y todos los archivos a=
djuntos. 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 alte=
rado.


This email originated from outside of Sony Music.  Do not click links or op=
en attachments unless you recognize the sender and know the content is safe=
.