Re: Altering a table - positioning new columns - Mailing list pgsql-general

Speaking only for myself, we suffer enough already from ODI dependency
hell, because of the largish number of functions, triggers and views
that are interdependent in one of our systems.

Just having to change the base table schemas slightly leads to a whole
round of drops and re-creates of the above objects. On a heavy
production system, this tends to be a pain.

Perhaps there is a way around this issue that we are not knowing about?

In terms of needing the columns to be in a certain order, while it may
look nice for documentation purposes, in the main scheme of things, we
have gotten used to column displays showing in the order that we made
them. If we have the time at any point, we tend to backup the table,
adjust the schema by hand, and re-import the data once again.

- Ericson Smith
http://www.did-it.com
http://weightlossfriends.com

On Tue, 2003-01-21 at 18:43, will trillich wrote:
> On Mon, Jan 20, 2003 at 08:38:18PM +0100, Michael Meskes wrote:
> > On Mon, Jan 20, 2003 at 07:24:57AM -0600, Chris Boget wrote:
> > > ALTER TABLE blah ADD COLUMN newcol AFTER anothercol.
> > >
> > > Is something like this possible in PG?  Or are _all_ new columns
> > > added to the end of the column list?
> >
> > This is exactly the reason why you should almost always acces your data
> > through views. Makes life so much easier.
>
> i've been wondering about this philosophy for a while, now.
> maybe we should ALWAYS use views as the API, and never the
> underlying tables --
>
>     create table _things (
>         stamp  timestamp(0),
>         fld    bigint,
>         other  varchar(10)
>     );
>
>     create function show_fld(bigint)returns text as '
>     ...something like make digits into ##-######-### part number
>     ' language 'plpgsql';
>
>     create view things as
>     select
>         show_fld( fld ),
>         show_other( other ),
>         something_else( yada yada )
>     from
>         _things
>     ;
>
>     create rule things_add as
>     on insert to things
>     do instead (
>         insert into _things (
>             stamp,
>             fld,
>             other
>         ) values (
>             current_timestamp,
>             store_fld( NEW.fld ),
>             store_fld( NEW.other )
>         );
>     );
>
>     create rule things_edit as
>     on update to things
>     do instead (
>     ...
>     );
>
> i'm beginning to think that this "always use a view" should be
> done for ALL tables, even the lookup/validation tables. is it a
> serious performance issue? is there a good reason NOT to do
> this?
>
> this would also facilitate changes in the future, i'd think:
> relying on views in the application code, we can change the
> underlying tables (add some, remove some, alter they way they
> interconnect) but the program logic could stay the same.
>
> in some instances. :)
>
> whaddya think?
>
> --
> There are 10 kinds of people:
> ones that get binary, and ones that don't.
>
> will@serensoft.com
> http://sourceforge.net/projects/newbiedoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> Looking for a firewall? Do you think smoothwall sucks? You're
> probably right... Try the folks at http://clarkconnect.org/ !
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html