pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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