Can not amend users details [solved]

[empusa]

I have recently switched to a new hosting company due to security and reliability problems with the previous host and have copied most of my sites across from the old server.

My new server uses PostGreSQL 8. I prefer to use Postgres as the servers are always quieter than the MySQL ones and should therefore be faster and more reliable 

All of my new sites however have one problem. When I go into the Users Manager I get the following error:

Error: operator does not exist: character varying = interger
Line 3: INNER JOIN elx_core_acl_aro AS aro ON aro.value = a.id
Hint: No operator matches the given name and arguements types(s). You might need to add explicit casts.

All of the sites worked fine on the old server (which used Postgres 7)

This error is even occurring on a fresh install of Elxis

[datahell]

We have already solved this issue but we have nt yet released the patch.
Open administrator/components/com_users/admin.users.php
Go around line 171-180, you will find something like this:

   $query = "SELECT DISTINCT a.*, g.name AS groupname FROM #__users a"
   ."\n INNER JOIN #__core_acl_aro aro ON aro.value = a.id"
   ."\n INNER JOIN #__core_acl_groups_aro_map gm ON gm.aro_id = aro.aro_id"
   ."\n INNER JOIN #__core_acl_aro_groups g ON g.group_id = gm.group_id";

Replace it with this:
   $pg = (eregi('postgre', $database->_resource->databaseType)) ? 1 : 0;
   $idjoin = ($pg) ? 'aro.value = a.id::VARCHAR' : 'aro.value = a.id';
   $query = "SELECT DISTINCT a.*, g.name AS groupname FROM #__users a"
   ."\n INNER JOIN #__core_acl_aro aro ON ".$idjoin
   ."\n INNER JOIN #__core_acl_groups_aro_map gm ON gm.aro_id = aro.aro_id"
   ."\n INNER JOIN #__core_acl_aro_groups g ON g.group_id = gm.group_id";

Save the file, done.

[empusa]

Many thanks.

I can confirm that the patch works.

Pete