MyBB Community Forums
[F] Missing PostgreSQL concat function - Printable Version

+- MyBB Community Forums (https://community.mybb.com)
+-- Forum: Community Archive (https://community.mybb.com/forum-106.html)
+--- Forum: Archived Forums (https://community.mybb.com/forum-143.html)
+---- Forum: Archived Development and Support (https://community.mybb.com/forum-155.html)
+----- Forum: Archived Bug Reports (https://community.mybb.com/forum-74.html)
+------ Forum: MyBB 1.4.2 (https://community.mybb.com/forum-100.html)
+------ Thread: [F] Missing PostgreSQL concat function (/thread-39382.html)



[F] Missing PostgreSQL concat function - DoctorCasino - 2008-10-21

MyBB Version: 1.4.2
PHP Version: 4.3.10
DB Version: PostgreSQL 8.3.3

I often got an SQL error, which says that the function concat does not exist.

e.g. in:
/admin/index.php?module=user/users&action=search&results=1&conditions=a:1:{s:9"usergroup";s:1:"5";}

when I wanted to watch users that are not activated.

And in PostgreSQL there is no concat function. Since there is no way for varying parameters in PostgreSQL function (until now), I created my own concat functions. Since I'm new to MyBB, I don't know how many arguments could be used for concat by MyBB. I've got functions for a maximum of four parameters.

After I created theses functions everthing worked fine

Maybe one could create these functions when installing MyBB:

CREATE OR REPLACE FUNCTION concat(text, text) RETURNS text AS $$
	SELECT $1 || $2;
$$ LANGUAGE 'sql';


CREATE OR REPLACE FUNCTION concat(text, text, text) RETURNS text AS $$
	SELECT $1 || $2 || $3;
$$ LANGUAGE 'sql';


CREATE OR REPLACE FUNCTION concat(text, text, text, text) RETURNS text AS $$
	SELECT $1 || $2 || $3 || $4;
$$ LANGUAGE 'sql';



RE: Missing PostgreSQL concat function - Ryan Gordon - 2008-10-21

We use switch statements on the db engine that is currently being used to use the right query


RE: Missing PostgreSQL concat function - DoctorCasino - 2008-10-21

(2008-10-21, 04:20 PM)Ryan Gordon Wrote: We use switch statements on the db engine that is currently being used to use the right query

I see, so I dropped my functions to get back to the errors:

This happens when I am in the admin dashbord and I click on the users awaiting activation:

Quote:SQL Error:
0 - ERROR: function concat(unknown, character varying, unknown) does not exist at character 144 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Query:
SELECT COUNT(u.uid) AS num_results FROM mybb_users u WHERE 1=1 AND (u.usergroup IN (5) OR ','||additionalgroups||',' LIKE '%,5,%'OR CONCAT(',',additionalgroups,',') LIKE '%,5,%')



RE: Missing PostgreSQL concat function - Ryan Gordon - 2008-10-22

switch($db->type)
			{
				case "pgsql":
				case "sqlite3":
				case "sqlite2":
					$additional_sql .= " OR ','||additionalgroups||',' LIKE '%,{$usergroup},%'";
				default:
					$additional_sql .= "OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
			}

Not sure what's wrong. Looks like you've made some modifications


RE: Missing PostgreSQL concat function - DoctorCasino - 2008-10-22

(2008-10-22, 01:56 AM)Ryan Gordon Wrote:
switch($db->type)
			{
				case "pgsql":
				case "sqlite3":
				case "sqlite2":
					$additional_sql .= " OR ','||additionalgroups||',' LIKE '%,{$usergroup},%'";
				default:
					$additional_sql .= "OR CONCAT(',',additionalgroups,',') LIKE '%,{$usergroup},%'";
			}

Not sure what's wrong. Looks like you've made some modifications

I can't test it right now, but I think there should be a

break;

after the first three cases!?
Ok, I added a

break;

at line 1926 and it works properly now.


RE: Missing PostgreSQL concat function - Ryan Gordon - 2008-10-22

Ah, woops - Must have missed that


[F] Missing PostgreSQL concat function - Dennis Tsang - 2008-10-24

Thank you for your bug report.

This bug has been fixed in our internal code repository. Please note that the problem will not be fixed here until these forums are updated.

With regards,
MyBB Group


RE: [F] Missing PostgreSQL concat function - Michael S. - 2008-11-16

This fix seems to be missing in SVN.


RE: [F] Missing PostgreSQL concat function - Ryan Gordon - 2008-11-17

The fix should be in the SVN now