Обсуждение: How to change all owners on all objects in a schema
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
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
"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
"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
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>