Please have a look at these two threads:
http://www.simplemachines.org/community/index.php?topic=292834.0
http://www.simplemachines.org/community/index.php?topic=292836.0
Both problems occur because of the implecit "integer to boolean"-conversion of MySQL missing in Postgres. These can be solved easily, as you can see in theses threads.
A bigger problem is the bad SQL-query found in the first thread. I already explained this in that thread, but I will explain it again here: In ManageNews.php, there are two places where this can be seen:
WHERE (bg.cannot_access = {int:cannot_access} OR bg.cannot_login = {int:cannot_login})
AND (COALESCE(bg.expire_time, 1=1) OR bg.expire_time > {int:current_time})
The phrase after the "AND" is bogus, because it will always evaluate to TRUE. The COALESCE-function will always return TRUE (or "bg.expire_time", which is an integer and will be converted to boolean by MySQL (but not by Postgres, which is another problem)) and because of the "OR" the whole part after the AND will always return true.
The problem there is the fact that this query will NOT return all the currently banned members. It will return all members who where banned at any time, even when they are not banned anymore. This is not what it should do.
My suggestion would be to change this query to: WHERE (bg.cannot_access = {int:cannot_access} OR bg.cannot_login = {int:cannot_login})
AND (bg.expire_time IS NULL OR bg.expire_time > {int:current_time})
This will also fix the problem with PostgreSQL.
The problem found in the second linked thread is also Postgres-related:
In the file Subs.php, Line 431, you can find this:
$condition = '1';
This variable will be later used inside a WHERE-clause of a query. This again is not supported by PostgreSQL, so you could just change this to:
$condition = '1=1';
(Or just write "TRUE").
Thx, added both issues to bugtracker.
http://dev.simplemachines.org/mantis/view.php?id=3118
I think I'm getting a similar problem when trying to delete members:
ERROR: operator does not exist: boolean <> integer
LINE 2: ...id_group = 1 OR FIND_IN_SET(1, additional_groups) != 0 THEN ...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
File: /usr/local/apache-tomcat6.0/webapps/ROOT/forums/Sources/Subs-Members.php
Line: 139
I see, this is another bug! (sorry, I didn't see your post before).
It wasn't too easy to find this: The problem is that there is no FIND_IN_SET() function in PostgreSQL. Because of this, simplemachines creates a stored procedure in your database when installing the forum.
File install_2-0_postgresql.sql, Line 40:
CREATE OR REPLACE FUNCTION FIND_IN_SET(text, text) RETURNS boolean AS
'SELECT
COALESCE($1 = ANY(STRING_TO_ARRAY($2, '','')), FALSE) AS result'
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION FIND_IN_SET(integer, character varying) RETURNS boolean AS
'SELECT
COALESCE(CAST($1 AS text) = ANY(STRING_TO_ARRAY($2, '','')), FALSE) AS result'
LANGUAGE 'sql';
As you can see, this function returns a boolean value, while this function in MySQL returns an integer. Because of this, the part of the query
FIND_IN_SET(1, additional_groups) != 0 compares a boolean with an integer when using PostgreSQL and this doesn't work.
So, what to do? It should work for PostgreSQL to just remove the "!= 0". This should work for MySQL, too, because in MySQL anything else than "0" is interpreted as "true" (implicit type cast). I guess this is the best way without touching the custom FIND_IN_SET-function. But personally, I would change the function to return a 1 or 0, instead of a boolean, to keep compatibility.
Of course, this should be posted as a seperate bug report...
The boolean != integer issue can be fixed by running the following queries in phpPgAdmin:
CREATE OR REPLACE FUNCTION bool_not_eq_int (boolean, integer) RETURNS boolean AS
'SELECT CAST($1 AS integer) != $2 AS result'
LANGUAGE 'sql';
Then
CREATE OPERATOR != (PROCEDURE = bool_not_eq_int, LEFTARG = boolean, RIGHTARG = integer);
This fix has already been committed to SVN, so it will be fixed in RC2.
Also, user-defined functions and stored procedures are two separate things.
I've created the bool_not_eq_int functions, which allows deleting a user to progress further. It gets to:
ERROR: column "recipient" does not exist
LINE 8: HAVING recipient IS null
^
File: /opt/forum/Sources/PersonalMessage.php
Line: 2551
RC1-1
That's another bug that has been fixed for RC2.
There were a few bugs with that query, so to fix all of them...
Replace the initial SELECT line with this:
SELECT pm.id_pm AS sender, pmr.id_pm
Then replace the GROUP BY and HAVING lines with this:
GROUP BY sender, pmr.id_pm
HAVING pmr.id_pm IS null,
Finally, change the code on line 2554 to this:
$remove_pms[] = $row['sender'];