Skip site navigation (1) Skip section navigation (2)

Re: Some useful plpgsql

From: "Berend Tober" <btober(at)seaworthysys(dot)com>
To: <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Some useful plpgsql
Date: 2003-02-28 21:23:43
Message-ID: 65451.66.212.203.144.1046467423.squirrel@$HOSTNAME (view raw or flat)
Thread:
Lists: pgsql-general
>> 1) To change the schema associated with given tables.
>
> I think this is likely to break things, particularly if it's used to
> move individual tables and not the entire contents of a schema.  I
> don't see anything here that will rename a table's rowtype into the new
> schema, and I don't see any guarantee that a table's indexes will move

You're absolutely correct!

The problem was that I (successfully) used a SIMILAR technique which
involved more manual inspection of intermediate steps to accomplish this,
and then I lost some important details in the translation trying to make
a more generic solution that others could use.

I think the function is still potentially useful, but you'ld have to run
a more general query, starting with something like

SELECT relname, alter_object_namespace('paid', relname)
FROM pg_catalog.pg_class

and then play with it a bit in order to better identify the objects you
want to effect, such as


SELECT
  relname,
  relowner,
  ( SELECT usename FROM pg_catalog.pg_user WHERE usesysid=relowner) as
usename,
  alter_object_namespace('paid', relname)
FROM pg_catalog.pg_class

followed by


SELECT
  relname,
  relowner,
  ( SELECT usename FROM pg_catalog.pg_user WHERE usesysid=relowner) as
usename,
  alter_object_namespace('paid', relname)
FROM pg_catalog.pg_class
WHERE relowner = [oid_of_relation_owner]


Then output the results to a text file and delete entries by hand that
were not of interest. Seems like a hell of a process, but I didn't see an
alternative besides re-creating the database one table at a time with the
new schema names in place.

~Berend Tober




In response to

pgsql-general by date

Next:From: Dmitry TkachDate: 2003-02-28 21:56:23
Subject: Re: postgres error reporting
Previous:From: Ericson SmithDate: 2003-02-28 21:03:43
Subject: Re: Recreating a primary key

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group