Re: full featured alter table?

Greg Stark wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
>> It is already a bad idea to use * in applications in case something does
>> change the order or number of columns. * is mostly going to be used in adhoc
>> queries where you care about saving some typing.
>
> Since this seems to have gone by without any contention I'll throw in 2c on
> the other side of this argument.
>
> I agree 100% with the logic but reach the opposite conclusion. One of the main
> goals of program design is to isolate the places that would have to be edited
> to accomplish any changes.
>
> If you explicitly list every or nearly every column in your queries, then
> whenever a new attribute is added you'll have to go through and add the new
> attribute to every query that fetched it.

Disagreed. There can not be any "query that fetched it" because it
didn't exist before. And if the surrounding application issuing that
query didn't have that attribute before, what's it going to do with it
now? It will hopefully just swallow it silently.

>
> Whereas if you use select * and make the presentation code handle any columns
> that appear in the result then adding a new column can be done purely in the
> database. The query and the presentation layer can adapt without changes.

Ah ... we are talking about presentation code only. I have seen code
that does a SELECT * from a table having multi-megabyte sized BLOB's
only to check IF FOUND afterwards ... saving a few keystrokes can be
expensive.

In the case you have dynamic presentation code that handles this sort of
thing, you are IMHO supposed to let it query a view. And this view has
to be a separate view just for this "presentation" (view is a synonym
for that, isn't it?). That way you are free to change the tables layout
in whatever way you want to, from adding/renaming/dropping columns,
splitting up the attributes into separate tables or changing data types
... whatever fits your "technical" needs. You can allways adjust the
view so that the presentation will be OK again.

>
> Of course it's not always possible to achieve this 100%. Often the
> presentation layer wants to do specific formatting for each attribute and
> doesn't want to display every single attribute. But using select * means
> there's one fewer place that needs to be edited to effect the new column.

Using SELECT * means that there is one more place to carefully check for
possible side effects.

>
> This all depends on having a driver that gives you an API that lets you access
> columns by name rather than position though.

You need an API that gives you access to the list of attribute names
first, and then dynamically access those attributes. PL/pgSQL for one
does not have this capability.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #