Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 2C982B5DBE1
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 23 Jun 2011 18:03:12 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 05784-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 23 Jun 2011 21:03:06 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ww0-f50.google.com (mail-ww0-f50.google.com
 [74.125.82.50])
	by mail.postgresql.org (Postfix) with ESMTP id BAA6EB5DBD2
	for <pgsql-admin@postgresql.org>; Thu, 23 Jun 2011 18:03:05 -0300 (ADT)
Received: by wwe6 with SMTP id 6so2019527wwe.19
	for <pgsql-admin@postgresql.org>; Thu, 23 Jun 2011 14:03:05 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:in-reply-to:references:date
	:message-id:subject:from:to:cc:content-type;
	bh=diHK4qJH4SWmSc7EMVX5XjqF/ecb6yb+889WELSUhtU=;
	b=FamhJDCww784kxYeWebgGdH4igT6lAkBgqWRALQz/gxJjBpty/RCWRDmYqhOLJ/MnL
	XyYvxaJGcztglSNT/euOroZw4jTitROrVMDuRfDGq481wCOpiYu/v39inu1dKoca843s
	+THR+xcFvkQ2ZhD2g0xtS+fKr7NyD+WTQ4Idg=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=wnzNxFWeNLq3r17a/MufrUbSrnFb7jTCRNWCDsk0PBeFeuK4Y5yvCiaN+2JN2iN0p9
	14ag+7wfV69KowevcKDf89YVK8ekQQOpnDADhfZD18/YSVyWEJwaDoys4U5QioKI0+af
	PxYHch8qdJ8i/+pAMrKueS1Jwr4gOjyO4R9ek=
MIME-Version: 1.0
Received: by 10.216.229.210 with SMTP id h60mr3833189weq.29.1308862984967;
	Thu, 23 Jun 2011 14:03:04 -0700 (PDT)
Received: by 10.216.236.20 with HTTP; Thu, 23 Jun 2011 14:03:04 -0700 (PDT)
In-Reply-To: <4E035EB8020000250003EB46@gw.wicourts.gov>
References: 
 <B75CD08C73BD3543B97E4EF3964B7D70077BB4@CITESMBX2.ad.uillinois.edu>
	<4E035E20020000250003EB41@gw.wicourts.gov>
	<4E035EB8020000250003EB46@gw.wicourts.gov>
Date: Thu, 23 Jun 2011 15:03:04 -0600
Message-ID: <BANLkTimHR07oOuqeUp-ix73tnxw8zbbL6w@mail.gmail.com>
Subject: Re: How to change all owners on all objects in a schema
From: Greg Spiegelberg <gspiegelberg@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Lance Campbell <lance@illinois.edu>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary=0016364d203147563504a66769f7
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.897 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001,
	RFC_ABUSE_POST=0.001
X-Spam-Level: 
X-Archive-Number: 201106/139
X-Sequence-Number: 37765

--0016364d203147563504a66769f7
Content-Type: text/plain; charset=ISO-8859-1

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>

--0016364d203147563504a66769f7
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">On Thu, Jun 23, 2011 at 2:41 PM, Kevin Grittner =
<span dir=3D"ltr">&lt;<a href=3D"mailto:Kevin.Grittner@wicourts.gov">Kevin.=
Grittner@wicourts.gov</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_q=
uote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1e=
x;">
&quot;Kevin Grittner&quot; &lt;<a href=3D"mailto:Kevin.Grittner@wicourts.go=
v">Kevin.Grittner@wicourts.gov</a>&gt; wrote:<br>
<br>
&gt; =A0 =A0 =A0 =A0 =A0 =A0 =A0|| &#39; set schema newschema;&#39;<br>
<br>
Oops; you wanted to change the owner, but I&#39;ll leave that as an<br>
exercise for the reader. =A0:-)<br>
<div><div></div><div class=3D"h5"><br></div></div></blockquote><div><br></d=
iv><div>Beat me to it =A0:)</div><div><br></div><div>Replace &lt;SCHEMA&gt;=
, &lt;NEW_OWNER&gt; and &lt;DATABASE&gt;</div><div><br></div><div>psql -qAt=
 -d=A0<meta http-equiv=3D"content-type" content=3D"text/html; charset=3Dutf=
-8">&lt;DATABASE&gt;=A0-c &quot;SELECT &#39;ALTER &#39;||quote_ident(n.nspn=
ame)||&#39;.&#39;||case when c.relkind=3D&#39;r&#39; then &#39;TABLE&#39; e=
lse &#39;SEQUENCE&#39; END||&#39; public.&#39;||quote_ident(relname)||&#39;=
 OWNER TO &lt;NEW_OWNER&gt;;&#39;</div>
</div><div><div>=A0 FROM pg_class c, pg_catalog.pg_namespace n</div><div>=
=A0WHERE c.relkind IN (&#39;r&#39;,&#39;S&#39;)</div><div>=A0 =A0AND c.reln=
amespace=3Dn.oid</div><div>=A0 =A0AND n.nspname=3D&#39;&lt;SCHEMA&gt;&#39;;=
&quot; | psql -qAt -d &lt;DATABASE&gt;</div>
</div><div><br></div><div><br></div>

--0016364d203147563504a66769f7--