Re: Altering a table - positioning new columns

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(at)serensoft(dot)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/ !