pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to change all owners on all objects in a schema
4+ messages / 3 participants
[nested] [flat]
* How to change all owners on all objects in a schema
@ 2011-06-23 15:59 Campbell, Lance <lance@illinois.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Campbell, Lance @ 2011-06-23 15:59 UTC (permalink / raw)
To: pgsql-admin
PostgreSQL: 9.0.x
How do I change the ownership of all objects within a schema from X to Y? The objects within each schema would be tables and sequences.
Thanks,
Lance Campbell
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to change all owners on all objects in a schema
@ 2011-06-23 20:39 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Campbell, Lance <lance@illinois.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2011-06-23 20:39 UTC (permalink / raw)
To: Lance Campbell <lance@illinois.edu>; pgsql-admin
"Campbell, Lance" <lance@illinois.edu> wrote:
> PostgreSQL: 9.0.x
>
> How do I change the ownership of all objects within a schema from
> X to Y? The objects within each schema would be tables and
> sequences.
I would use DO statements. What could be done as a one-liner to
handle the tables is shown below with line breaks for readability.
do $$
declare stmt text;
begin
for stmt in
select 'alter table oldschema.'
|| quote_ident(relname)
|| ' set schema newschema;'
from pg_class
where relkind = 'r'
and relnamespace =
(
select oid from pg_catalog.pg_namespace
where nspname = 'oldschema'
)
loop
raise notice '%', stmt;
end loop;
end;
$$;
Similar for sequences, only ALTER SEQUENCE and relkind = 'S'.
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to change all owners on all objects in a schema
@ 2011-06-23 20:41 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2011-06-23 20:41 UTC (permalink / raw)
To: Lance Campbell <lance@illinois.edu>; pgsql-admin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> || ' set schema newschema;'
Oops; you wanted to change the owner, but I'll leave that as an
exercise for the reader. :-)
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to change all owners on all objects in a schema
@ 2011-06-23 21:03 Greg Spiegelberg <gspiegelberg@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Greg Spiegelberg @ 2011-06-23 21:03 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Lance Campbell <lance@illinois.edu>; pgsql-admin
On Thu, Jun 23, 2011 at 2:41 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
>
> > || ' set schema newschema;'
>
> Oops; you wanted to change the owner, but I'll leave that as an
> exercise for the reader. :-)
>
>
Beat me to it :)
Replace <SCHEMA>, <NEW_OWNER> and <DATABASE>
psql -qAt -d <DATABASE> -c "SELECT 'ALTER
'||quote_ident(n.nspname)||'.'||case when c.relkind='r' then 'TABLE' else
'SEQUENCE' END||' public.'||quote_ident(relname)||' OWNER TO <NEW_OWNER>;'
FROM pg_class c, pg_catalog.pg_namespace n
WHERE c.relkind IN ('r','S')
AND c.relnamespace=n.oid
AND n.nspname='<SCHEMA>';" | psql -qAt -d <DATABASE>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-06-23 21:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-23 15:59 How to change all owners on all objects in a schema Campbell, Lance <lance@illinois.edu>
2011-06-23 20:39 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-23 20:41 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-23 21:03 ` Greg Spiegelberg <gspiegelberg@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox