X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 005BC52B3F for ; Mon, 1 Aug 2005 13:48:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55885-01 for ; Mon, 1 Aug 2005 16:48:29 +0000 (GMT) Received: from localhost.localdomain (unknown [12.31.43.201]) by svr1.postgresql.org (Postfix) with ESMTP id 63B5A52B53 for ; Mon, 1 Aug 2005 13:48:28 -0300 (ADT) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.13.1/8.13.1) with ESMTP id j71GmVti004212 for ; Mon, 1 Aug 2005 12:48:31 -0400 Received: from localhost (localhost [[UNIX: localhost]]) by localhost.localdomain (8.13.1/8.13.1/Submit) id j71GmTDK004211 for pgsql-admin@postgresql.org; Mon, 1 Aug 2005 12:48:29 -0400 From: "Mark R. Dingee" To: pgsql-admin@postgresql.org Subject: Re: Alter Table vs. Rename/Create/Drop table with plpgsql functions. Date: Mon, 1 Aug 2005 12:48:25 -0400 User-Agent: KMail/1.7.1 References: <8A27D71C-CA88-4D33-9E19-F601A5E02526@lodestonetechnologies.com> In-Reply-To: <8A27D71C-CA88-4D33-9E19-F601A5E02526@lodestonetechnologies.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200508011248.26333.mark.dingee@cox.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.026 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/5 X-Sequence-Number: 18324 Robert, If you have the luxury of taking the production db offline for a few minutes or if you're just making changes in a test environment, this process works: given: create table test1 ( id integer, txt1 text, txt2 text ); execute at command line: pg_dump [dbname]> backups/renametable.sql use your favorite text editor to change the above create table statement in backups/renametable.sql to: create table test1 ( id integer, txt0 text, txt1 text, txt2 text ); execute at command line: dropdb [dbname]; createdb [dbname]; cat backups/renametable.sql | psql it's not the most graceful of methods, but it works. Mark On Monday 01 August 2005 12:06 pm, Robert Perry wrote: > Perhaps I am just just a bit anal on this but some columns I > really like to have as the last columns of a table. (usually > last_modby, last_modtime, type fields) Thus when I need to add a > column to a table I am not happy just adding the column. Instead I > go a little insane and rename the table, create the new table, copy > the data into the new table (with any require manipulations), change > all foreign key constraints to point to the new table, rebuild > plpgsql functions the user that table and finally drop the old table. > > The problem is the second to last. (rebuild plpgsql function) > These function are getting a little harder to find. This DB has > hundreds if not thousands of functions and some of my table names are > a bit common. (e.g. item) My first of all I would really like a way > to tell postgresql to un cache all functions and let the cache > rebuild as need be. I imagine restarting postgresql would do this, > but in my particular situation that has its' own problems associated > with it. > > Secondly does anyone know a way to insert a column instead of > appending a column to a table. That way I could just avoid the > entire mess. > > Thanks > Robert Perry > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match