Обсуждение: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
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.
Greeting.
Вложения
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.
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.
Hi David,
Why do you say that reassined operation don’t work for me?
This operation is for assign an account for execute all modifications required over the environment.
Greetings
De: David G. Johnston <david.g.johnston@gmail.com>
Enviado el: martes, 18 de junio de 2024 15:39
Para: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
CC: pgsql-admin@lists.postgresql.org
Asunto: Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
On Tuesday, June 18, 2024, PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.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.
Have you determined that “reassigned owned” won’t work for you?
David J.
ADVERTENCIA: E-Mail externo, favor verifique remitente, no descargue archivos adjuntos de remitentes desconocidos, no haga Clic en enlaces. Ante sospechas reporte a Seguridad de Información Itau seguridadinformacion@itau.cl
Вложения
Following scripts will take care to change schema owner
Mk_altr_proc_owner.sql ( copy past this SQL statement)
SELECT ' alter procedure '||rtrim(nspname)||'.'||ltrim( proname )||' owner to targetschema;'
FROM pg_catalog.pg_namespace
JOIN pg_catalog.pg_proc
ON pronamespace = pg_namespace.oid
WHERE nspname = 'yourschemaname
ORDER BY Proname ;
Psql -h hostname -U ursename -d dbname -t -A -f mk_altr_proc_owner.sql -o altr_proc_owner.sql
Psql -h hostname -U username -d dbname -f altr_proc_woner.sql -o alter_proc_owner.log
--Raju
From: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
Sent: Tuesday, June 18, 2024 3:47 PM
To: 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
Hi David,
Why do you say that reassined operation don’t work for me?
This operation is for assign an account for execute all modifications required over the environment.
Greetings
De: David G. Johnston <david.g.johnston@gmail.com>
Enviado el: martes, 18 de junio de 2024 15:39
Para: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
CC: pgsql-admin@lists.postgresql.org
Asunto: Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
On Tuesday, June 18, 2024, PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.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.
Have you determined that “reassigned owned” won’t work for you?
David J.
ADVERTENCIA: E-Mail externo, favor verifique remitente, no descargue archivos adjuntos de remitentes desconocidos, no haga Clic en enlaces. Ante sospechas reporte a Seguridad de Información Itau seguridadinformacion@itau.cl
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.
Вложения
On Tuesday, June 18, 2024, PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.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.
Have you determined that “reassigned owned” won’t work for you?
Following scripts will take care to change schema owner
Mk_altr_proc_owner.sql ( copy past this SQL statement)
SELECT ' alter procedure '||rtrim(nspname)||'.'||ltrim( proname )||' owner to targetschema;'
Psql -h hostname -U ursename -d dbname -t -A -f mk_altr_proc_owner.sql -o altr_proc_owner.sql
On Tue, Jun 18, 2024 at 3:39 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tuesday, June 18, 2024, PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.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.
Have you determined that “reassigned owned” won’t work for you?That's a pretty blunt club. Changes the ownership of EVERYTHING, no?
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.
, proargnames)
from pg_proc
where pronamespace::regnamespace = 'some_schema';
I provided the scripts , use , how ever you like
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Tuesday, June 18, 2024 4:23 PM
To: lennam@incisivetechgroup.com
Cc: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>; pgsql-admin@lists.postgresql.org
Subject: Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
On Tue, Jun 18, 2024 at 12:58 PM <lennam@incisivetechgroup.com> wrote:
Following scripts will take care to change schema owner
Mk_altr_proc_owner.sql ( copy past this SQL statement)
SELECT ' alter procedure '||rtrim(nspname)||'.'||ltrim( proname )||' owner to targetschema;'
Maybe using quote_ident to prevent, unlikely as it may be, SQL injection issues. The trims seem likely to be unnecessary - catalog contents should be clean.
Psql -h hostname -U ursename -d dbname -t -A -f mk_altr_proc_owner.sql -o altr_proc_owner.sql
The script output file is nice to check one's works I guess. But if you are going to use psql there is the \gexec meta-command that makes this even easier.
David J.
On Tue, Jun 18, 2024 at 3:33 PM PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.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".
Hi David,
Thanks for your appointment, let me investigate and for all thanks you.
Greetings
De: David G. Johnston <david.g.johnston@gmail.com>
Enviado el: martes, 18 de junio de 2024 15:42
Para: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
CC: pgsql-admin@lists.postgresql.org
Asunto: Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
On Tuesday, June 18, 2024, PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl> wrote:
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.
pg_identify_object
David J.
ADVERTENCIA: E-Mail externo, favor verifique remitente, no descargue archivos adjuntos de remitentes desconocidos, no haga Clic en enlaces. Ante sospechas reporte a Seguridad de Información Itau seguridadinformacion@itau.cl
Вложения
On Tuesday, June 18, 2024, PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl> wrote:
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.
pg_identify_object
schema | public
name |
identity | public."i.am.a.function"(pg_catalog.text,integer)
oid | 16389
proname | i.am.a.function
"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
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 han omitido intencionalmente los signos y acentosdiacriticos del idioma castellano. La informacion contenida en este mensaje y cualquier archivo adjunto es confidencialy no puede ser usada por mas personas que sus destinatarios. El uso no autorizado de esta informacion puede sersancionado de conformidad con el Codigo Penal chileno. Si ha recibido este correo por error, por favor notifique al remitenterespondiendo este mismo mensaje y elimine el mensaje y todos los archivos adjuntos. Internet no puede garantizarla integridad de este mensaje, por lo que el Banco no se hace responsable si el contenido del mismo ha sido alterado.
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.
I would this this is what you are looking for