Simple Machines Community Forum

2.0b4 Centos 5.1 Postgresql 8.3.3

After installing and selecting a new language i get this error when i click on a board link:

ERROR: function instr(text, unknown) does not exist at character 361
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Arquivo: /var/www/html/example.com/Sources/MessageIndex.php
Linha: 248

The code is this:

        // Nosey, nosey - who's viewing this topic?
        if (!empty($settings['display_who_viewing']))
        {
                $context['view_members'] = array();
                $context['view_members_list'] = array();
                $context['view_num_hidden'] = 0;

                $request = $smcFunc['db_query']('', '
                        SELECT
                                lo.id_member, lo.log_time, mem.real_name, mem.member_name, mem.show_online,
                                mg.online_color, mg.id_group, mg.group_name
                        FROM {db_prefix}log_online AS lo
                                LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = lo.id_member)
                                LEFT JOIN {db_prefix}membergroups AS mg ON (mg.id_group = CASE WHEN mem.id_group = {int:reg_member_group\
} THEN mem.id_post_group ELSE mem.id_group END)
                        WHERE INSTR(lo.url, {string:in_url_string}) OR lo.session = {string:session}',
                        array(
                                'reg_member_group' => 0,
                                'in_url_string' => 's:5:"board";i:' . $board . ';',
                                'session' => $user_info['is_guest'] ? 'ip' . $user_info['ip'] : session_id(),
                        )
                );


Selecting the default english language does not get rid of the error.

As i saw this:
if (!empty($settings['display_who_viewing']))

I disabled "Show who is viewing the board index and posts" in the Theme Settings and obviously the error is gone since that chunk of code is not executed.

Thanks, Bug #2657: instr does not exist in postgresql (http://dev.simplemachines.org/mantis/view.php?id=2657)

I think there is two bugs here. I am not really sure on that though.

I'm not positive that the "unknown" type is a bug, but anyway, run this in phpPgAdmin and let me know if it fixes the issue:

CREATE OR REPLACE FUNCTION instr(text, text)
RETURNS integer AS '
  SELECT POSITION($2 IN $1)
' IMMUTABLE STRICT LANGUAGE 'sql';

(Source: MySQL Compatibility Functions package at http://pgfoundry.org/projects/mysqlcompat/)

After creating the function i got this error:

ERROR: argument of OR must be type boolean, not type integer
Arquivo: /var/www/html/example.com/Sources/MessageIndex.php
Linha: 248

Then i changed from:
WHERE INSTR(lo.url, {string:in_url_string}) OR lo.session = {string:session}',

To:
WHERE INSTR(lo.url, {string:in_url_string}) > 0 OR lo.session = {string:session}',

And it worked. Then when viewing the post:

ERROR: argument of OR must be type boolean, not type integer
Arquivo: /var/www/html/example.com/Sources/Display.php
Linha: 497

The same fix as above worked.

Thanks for the feedback. With some minor tweaking, we can make the function return a boolean value :)

CREATE OR REPLACE FUNCTION instr(text, text)
RETURNS boolean AS '
  SELECT (POSITION($2 IN $1) != 0)
' IMMUTABLE STRICT LANGUAGE 'sql';

That's the version I've committed as the fix :)

! PostgreSQL doesn't have instr() (install_2-0_postgresql.sql, upgrade_2-0_postgresql.sql) [Bug 2657]

Revision 7598

In my proposed fix i changed the where clause and not the function because i though it would be possible that the function was used somewhere else with the original meaning, that is, return the substring start position (int) in a string.

Thanks for the fast fix.

It should be returning as the position. That is the way the MySQL manual says it should. So we need to follow that incase there is another use for instr such as returning the actual position in the string.