X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 0F8D5D1B42F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Dec 2003 13:24:53 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 39056-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 10 Dec 2003 09:24:25 -0400 (AST)
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by svr1.postgresql.org (Postfix) with ESMTP id F1C8BD1B4A8
	for <pgsql-sql@postgresql.org>; Wed, 10 Dec 2003 09:24:21 -0400 (AST)
Received: from [61.94.40.113] (helo=[192.168.0.4])
	by noon.pghoster.com with asmtp (Exim 4.24) id 1AU4K1-0005vd-AO
	for pgsql-sql@postgresql.org; Wed, 10 Dec 2003 07:24:24 -0600
Subject: How to completely move a table to another schema?
From: Chris Travers <chris@travelamericas.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: 
Message-Id: <1071041216.2212.73.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.2 (1.2.2-4) 
Date: 10 Dec 2003 20:21:08 +0800
Content-Transfer-Encoding: 7bit
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - travelamericas.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/82
X-Sequence-Number: 16248

Hi all;

I have a function which moves a table from one schema to another by
updating the relnamespace field of pg_class:

CREATE OR REPLACE FUNCTION move_relation(VARCHAR, VARCHAR, VARCHAR)
RETURNS BOOL
AS '
-- $1 is the table name
-- $2 is the source schema
-- $3 is the destination schema
--
        UPDATE pg_catalog.pg_class
        SET relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
                                WHERE nspname = $3)
        WHERE relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
                                WHERE nspname = $2)
                AND relname = $1;
                                   
	UPDATE pg_catalog.pg_type
        SET typnamespace = (SELECT oid FROM pg_catalog.pg_namespace
                                WHERE nspname = $3)
        WHERE typnamespace = (SELECT oid FROM pg_catalog.pg_namespace
                                WHERE nspname = $2)
                AND typname = $1;
                                                                                
        SELECT TRUE;
' LANGUAGE SQL;
                                                     
Am I missing anything?  I have already had a few problems that led me to discover 
that I needed to put in the second update query.  Just figured I would check.

Best Wishes,
Chris Travers