Re: change natural column order - Mailing list pgsql-general

>> > SELECT * is almost always bad style. It shouldnt be so hard to
> This is another religious issue you'll find people pretty adamant on both
> sides.

    Seems so.

> I tend to prefer to use "SELECT *" because it reduces repetition and
> improves
> modularity. There are fewer places in the code that need to know about a
> new
> column being added to a table (or expression to a query) and fewer
> places that
> need to know about a new column (or expression) being needed in the final
> result.

    Same here. Simplifies request generation, simplifies coding, less
change-tracking...

> [I am assuming you use look up columns by name. To me it seems the only
> reasonable approach for all but the simplest cases]

    Of course. SELECT * and then using the columns by number is, well, like
dropping a brick and hoping it doesnt land on your foot.

> However many databases and interfaces have some pretty strong practical
> problems that result from using it. So it's pretty standard DBA
> rule-of-thumb
> material to discourage its use.

    For instance the Python MySQL has no such functionality. PHP-Mysql has,
though.

> The only problem I've run into with Postgres is that there's no way to
> *remove* a column from a list of columns without listing all the
> non-removed
> columns.

    True.
    In that case, the database library which generates queries, should offer
this kind of functionality, and generate the SELECT col1, ..., colN
appropriately (mine does).

    In fact I like SELECT * because I find generated queries to be,
sometimes, kludgey at best to manipulate when using JOIN's or complicated
WHEREs. Let me explain.
    In my DB library, I have classmethods to create a class instance from a
row. For instance, Class.GetByKey( value ) looks up on the primary key.
Which field is the primary key is declared once in the class definition.
GetByKey is part of the base class, of course (some kind of DbRow). Some
classes will have special methods, like fulltext search methods, most
recent search methods...
    So, the SQL is neatly hidden in these methods, but sometimes you do need
to mess with it :
    Then, I have something like Class.GetSQL( "SELECT m.* FROM mytable m,
othertable o WHERE some joins ... " )
which just executes the SQL string (with parameters if needed), retrieves
the rows as mappings of column_name=>value and instanciates the objects.
    By the way, all the Get...() classmethods are usually one line, calling
GetSQL().

> And there's no way to disambiguate if you add a second column by the
> same name.

    MySQL has an easy solution : columns with the same name overwrite one
another. Neat for joins on keys which have the same name, but still leaves
this "dropping a brick" feeling.

> So you have some situations where you can't add an expression with
> the correct name without explicitly listing every other column.

    Well, that's what DB libraries are for, ain't they ?

> There may be performance implications for having more columns than
> necessary
> in a select list as well.

    If you just need the product name, don't retrieve the description ;)
    I added an additional, optional parameter to specify the columns to
remove. In this case, the instance members are not created, and if you try
to use them, an exception is raised...