Re: Alter Table vs. Rename/Create/Drop table with plpgsql functions. - Mailing list pgsql-admin

     Thanks for the suggestion.  Dump/Reload the DB I am really not
going to be able to do.  Also I am going to be doing this to several
copies of similar databases so vi is out, but that is nothing that a
nice script can't overcome.  I think that the down time would just be
to much.  Also because I am going to be running it on multiple
datbases I was hoping to limit it to a single script ran inside of
psql, as I already have a system in place to run given sql scripts on
each databases.

     Other wise it is a very good suggestion that would same my much
trouble.  I may still come back to it.

Thanks
Robert Perry

On Aug 1, 2005, at 12:48 PM, Mark R. Dingee wrote:

> 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
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>