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

> SELECT * is almost always bad style. It shouldnt be so hard to

    Why ?

    Many languages, including PHP, have associative arrays, so you should
just use array[column_name] instead of array[column_number]. This is what
I do, all the time.

    For instance, in Python :

    * The wrong way :
    cursor.execute( "SELECT name, address, zipcode FROM people WHERE blah" )
    data = cursor.fetchone()
    name = data[0]
    address = data[1]
    zipcode = data[2]

    This is BAD because :
    - When your SELECT has more than a few columns, you have to be really
really careful about the order, and one day you'll mess it up and it'll
bite you. Same thing with parameters by number in printf !
    - When you add a column, you have to go through all the SELECTs in your
app that are not auto-generated
    - Including all the columns slows down query generation and query parsing.

    * The Very wrong way :
    cursor.execute( "SELECT * FROM people WHERE blah" )
    data = cursor.fetchone()
    name = data[0]
    address = data[1]
    zipcode = data[2]

    This is BAD because, when your table structure changes, your application
breaks.

    * How I do it :
    cursor.execute( "SELECT * FROM people WHERE blah" )
    data = cursor.dictfetchone()
    name = data['name']
    address = data['address']
    zipcode = data['zipcode']

    or :
    for key, value in data.items():
        print key, "=", value

    or instanciate a class and set its attributes:
    result = myclass()
    for key, value in data.items():
        setattr( result, key, myclass.type_converter[key](value) )

    The last being how a decent DB library would do it.

    I find this a lot better, because :
    - no need to generate and then parse long queries with all the columns
    - no worries about column order or adding columns
    - raises an exception if a column misses or has the wrong name
    - a lot simpler
    - a lot more explicit
    - you can auto-cast to and from the DB if your class has a table of type
converters indexed on the column name
    - etc...

    Also, in my case, it eases query generation a lot, I use the same code
for many tables.

    You can do this in PHP I believe with associative arrays...


    Now, to prove the point, take the following PHP code ripped out of the
osCommerce (which I don't consider an example of good programming, but
it's a good example here). It builds a SELECT wiht various parameters.
Now, tell me, if you access columns according to their number in the
result, what is the column number for the products_description ?
    If you access columns by their name, then it's just
$result['products_description']

<code class=spaghetti>

$select_column_list = '';

     for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
       switch ($column_list[$i]) {
         case 'PRODUCT_LIST_MODEL':
           $select_column_list .= 'p.products_model, ';
           break;
         case 'PRODUCT_LIST_NAME':
           $select_column_list .= 'pd.products_name,
pd.products_description, ';
           break;
         case 'PRODUCT_LIST_MANUFACTURER':
           $select_column_list .= 'm.manufacturers_name, ';
           break;
         case 'PRODUCT_LIST_QUANTITY':
           $select_column_list .= 'p.products_quantity, ';
           break;
         case 'PRODUCT_LIST_IMAGE':
           $select_column_list .= 'p.products_image, ';
           break;
         case 'PRODUCT_LIST_WEIGHT':
           $select_column_list .= 'p.products_weight, ';
           break;
       }
     }

// show the products of a specified manufacturer
     if (isset($HTTP_GET_VARS['manufacturers_id'])) {
       if (isset($HTTP_GET_VARS['filter_id']) &&
tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
         $listing_sql = "select " . $select_column_list . " p.products_id,
p.products_ready_to_ship, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
as specials_new_products_price, IF(s.status,
s.specials_new_products_price, p.products_price) as final_price from " .
TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left
join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where
p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and
m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and
p.products_id = p2c.products_id and pd.products_id = p2c.products_id and
pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" .
(int)$HTTP_GET_VARS['filter_id'] . "'";
       } else {
// We show them all
         $listing_sql = "select " . $select_column_list . " p.products_id,
p.products_ready_to_ship, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
as specials_new_products_price, IF(s.status,
s.specials_new_products_price, p.products_price) as final_price from " .
TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on
p.products_id = s.products_id where p.products_status = '1' and
pd.products_id = p.products_id and pd.language_id = '" .
(int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and
m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
       }
     } else {
// show the products in a given categorie
       if (isset($HTTP_GET_VARS['filter_id']) &&
tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
         $listing_sql = "select " . $select_column_list . " p.products_id,
p.products_ready_to_ship, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
as specials_new_products_price, IF(s.status,
s.specials_new_products_price, p.products_price) as final_price from " .
TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left
join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where
p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and
m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and
p.products_id = p2c.products_id and pd.products_id = p2c.products_id and
pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" .
(int)$current_category_id . "'";
       } else {
// We show them all
         $listing_sql = "select " . $select_column_list . " p.products_id,
p.products_ready_to_ship, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NU



</code>