PostgreSQL does not cast values between intergers and varchar by default [#555132]

Very nice module, congrats.
Minor problem under PostgreSQL 8.4

When connecting to:
users/admin

This message is displayed:

    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: l'opérateur n'existe pas : character varying = integer LINE 1: UPDATE users SET timezone_id = 320 WHERE timezone = 7200 AND... ^ HINT: Aucun opérateur ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. in /home/html/drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: UPDATE users SET timezone_id = 320 WHERE timezone = 7200 AND timezone_id = 0 in /home/html/drupal/sites/all/modules/event/event.module on line 275.

In users table, timezone is a character varying(8).
Very strange, because it includes integer values.

PostgreSQL does not cast values from varchar to integer automatically.
WHERE timezone = 7200 fails.

We need to cast the value in PHP before it is sent to the database.

Bye, Jean-Michel

Comment	File	Size	Author
#3	postgresql-event-cast.diff	614 bytes	grub3
#3
#2	postgresql-event-cast.diff	611 bytes	grub3
#2

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

grub3 CreditAttribution: grub3 commented 20 August 2009 at 20:40

I could use this patch:

Index: event.module
===================================================================
--- event.module	(révision 614)
+++ event.module	(copie de travail)
@@ -272,7 +272,7 @@
       // Lookup TZ ID based on TZ offset
       $timezone_id = event_timezone_map($user->timezone);
       // Update all users with the same offset.
-      db_query('UPDATE {users} SET timezone_id = %d WHERE timezone = %d AND timezone_id = 0', $timezone_id, $user->timezone);
+      db_query('UPDATE {users} SET timezone_id = %d WHERE timezone = CAST (%d AS VARCHAR) AND timezone_id = 0', $timezone_id, $user->timezone);
     }
   }
 }

This seem to be SQL99 safe, please apply after review.