agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedScripting a ALTER PROCEDURE or FUNCTION to Change OWNER
17+ messages / 6 participants
[nested] [flat]
* Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 19:33 PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
0 siblings, 3 replies; 17+ messages in thread
From: PABLO ANDRES IBARRA DUPRAT @ 2024-06-18 19:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
[cid:image001.png@01DAC194.AB27BE00]
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.
Attachments:
[image/png] image001.png (29.7K, ../../CPUP152MB401802939E39B07A3BD264A887CE2@CPUP152MB4018.LAMP152.PROD.OUTLOOK.COM/3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 19:39 David G. Johnston <david.g.johnston@gmail.com>
parent: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
2 siblings, 2 replies; 17+ messages in thread
From: David G. Johnston @ 2024-06-18 19:39 UTC (permalink / raw)
To: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 19:42 David G. Johnston <david.g.johnston@gmail.com>
parent: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
2 siblings, 2 replies; 17+ messages in thread
From: David G. Johnston @ 2024-06-18 19:42 UTC (permalink / raw)
To: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
>
>
>
>
See
https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-OBJECT
pg_identify_object
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 19:47 PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: PABLO ANDRES IBARRA DUPRAT @ 2024-06-18 19:47 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
[cid:image001.png@01DAC196.6041EF80]
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<mailto: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<mailto: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.
Attachments:
[image/png] image001.png (29.7K, ../../CPUP152MB40187B6AFA1563A62AC5DF0F87CE2@CPUP152MB4018.LAMP152.PROD.OUTLOOK.COM/3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 19:58 lennam@incisivetechgroup.com
parent: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
0 siblings, 1 reply; 17+ messages in thread
From: lennam@incisivetechgroup.com @ 2024-06-18 19:58 UTC (permalink / raw)
To: 'PABLO ANDRES IBARRA DUPRAT' <Pablo.Ibarra@itau.cl>; 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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 <mailto:david.g.johnston@gmail.com> >
Enviado el: martes, 18 de junio de 2024 15:39
Para: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl <mailto:Pablo.Ibarra@itau.cl> >
CC: pgsql-admin@lists.postgresql.org <mailto: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 <mailto: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 <mailto: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.
Attachments:
[image/png] image001.png (29.7K, ../../001401dac1b9$edd4aea0$c97e0be0$@incisivetechgroup.com/3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 20:22 Ron Johnson <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Ron Johnson @ 2024-06-18 20:22 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 20:23 David G. Johnston <david.g.johnston@gmail.com>
parent: lennam@incisivetechgroup.com
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2024-06-18 20:23 UTC (permalink / raw)
To: lennam@incisivetechgroup.com; +Cc: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>; pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 20:24 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2024-06-18 20:24 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Jun 18, 2024 at 1:23 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> 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?
>
Maybe that is what is wanted, or is sufficient. I found it plausible the
OP simply was unaware of its existence and should make sure it doesn't
actually meet their needs quickly and easily.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 20:28 Ron Johnson <ronljohnsonjr@gmail.com>
parent: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
2 siblings, 1 reply; 17+ messages in thread
From: Ron Johnson @ 2024-06-18 20:28 UTC (permalink / raw)
To: pgsql-admin
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".
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 20:31 lennam@incisivetechgroup.com
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: lennam@incisivetechgroup.com @ 2024-06-18 20:31 UTC (permalink / raw)
To: 'David G. Johnston' <david.g.johnston@gmail.com>; +Cc: 'PABLO ANDRES IBARRA DUPRAT' <Pablo.Ibarra@itau.cl>; pgsql-admin@lists.postgresql.org
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 <mailto: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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 20:34 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2024-06-18 20:34 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
On Tue, Jun 18, 2024 at 1:29 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> 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".
>
Should use %I whenever possible (and %L)
... PROCEDURE %I.%I ...
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 21:01 PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: PABLO ANDRES IBARRA DUPRAT @ 2024-06-18 21:01 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi David,
Thanks for your appointment, let me investigate and for all thanks you.
Greetings
[cid:image001.png@01DAC1A1.2E1B56D0]
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<mailto: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.
See https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-OBJECT
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<mailto: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.
Attachments:
[image/png] image001.png (29.7K, ../../CPUP152MB4018E15E5943ACE33FB19C9187CE2@CPUP152MB4018.LAMP152.PROD.OUTLOOK.COM/3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 21:02 David G. Johnston <david.g.johnston@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2024-06-18 21:02 UTC (permalink / raw)
To: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, Jun 18, 2024 at 12:42 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> 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.
>>
>>
>>
>>
> See
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-OBJECT
>
> pg_identify_object
>
>
>
Specifically:
select id.*, pg_proc.*, tableoid from pg_proc,
pg_identify_object(1255,oid,0) as id;
type | function
schema | public
name |
identity | public."i.am.a.function"(pg_catalog.text,integer)
oid | 16389
proname | i.am.a.function
[...]
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 21:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2024-06-18 21:08 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 21:14 PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 17+ messages in thread
From: PABLO ANDRES IBARRA DUPRAT @ 2024-06-18 21:14 UTC (permalink / raw)
To: 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>
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 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* RE: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 22:02 Vitale, Anthony, Sony Music <anthony.vitale@sonymusic.com>
parent: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
0 siblings, 1 reply; 17+ messages in thread
From: Vitale, Anthony, Sony Music @ 2024-06-18 22:02 UTC (permalink / raw)
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>
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.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 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER
@ 2024-06-18 23:45 David G. Johnston <david.g.johnston@gmail.com>
parent: Vitale, Anthony, Sony Music <anthony.vitale@sonymusic.com>
0 siblings, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2024-06-18 23:45 UTC (permalink / raw)
To: Vitale, Anthony, Sony Music <anthony.vitale@sonymusic.com>; +Cc: PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Tue, Jun 18, 2024 at 3:02 PM Vitale, Anthony, Sony Music <
anthony.vitale@sonymusic.com> wrote:
> I would this this is what you are looking for
>
For loops and SQL injection risks, not an ideal way to write SQL programs.
Though it does allow you to avoid using psql but in which case you need to
do away with the \set metacommands. If you are going to use psql I
strongly suggest running the select query, inspecting the results, then
changing said query to use \gexec. A lot fewer moving parts that dealing
with plpgsql.
Good call on making it dynamic on prokind though. And the owner matching.
Tom's got the right idea of just casting the OID for the main naming scheme
- it does the SQL injection mitigation.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-06-18 23:45 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-18 19:33 Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
2024-06-18 19:39 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-18 19:47 ` PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
2024-06-18 19:58 ` lennam@incisivetechgroup.com
2024-06-18 20:23 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-18 20:31 ` lennam@incisivetechgroup.com
2024-06-18 20:22 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-18 20:24 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-18 19:42 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-18 21:01 ` PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
2024-06-18 21:02 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-18 21:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-06-18 21:14 ` PABLO ANDRES IBARRA DUPRAT <Pablo.Ibarra@itau.cl>
2024-06-18 22:02 ` Vitale, Anthony, Sony Music <anthony.vitale@sonymusic.com>
2024-06-18 23:45 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-18 20:28 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-06-18 20:34 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox