The result on this page make use of MySQL specific "concat" function and hence fails with PostgresSQL with following error:

warning: pg_query() [function.pg-query]: Query failed: ERROR: function concat(int_unsigned, character varying) does not exist LINE 1: SELECT COUNT(DISTINCT(CONCAT(uid, hostname))) FROM accesslog ^ HINT: No function matches the given name and argument types. You may need to add explicit type casts. in MyPath\D6\includes\database.pgsql.inc on line 155.

Comments

seanr’s picture

seanr
he/him
commented

Don't know pgsql very well, but what happens if you do it like this?

SELECT COUNT(DISTINCT(uid || hostname AS hit))) FROM accesslog

Obviously that won;t work in mysql, so it'd have to be abstracted out somehow.

vivek.puri’s picture

vivek.puri commented

I didn't wanted to change code but I managed to find a work around by adding a function as:

CREATE OR REPLACE FUNCTION "concat"(int_unsigned, character varying) RETURNS character varying AS
'SELECT $1 || $2 ;'
LANGUAGE 'sql';
wolfie’s picture

wolfie commented

The function suggested by crystalcube has been added to HEAD. This report can/should be closed now.

chx’s picture

chx commented
Status: Active » Fixed
Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)
sirprize’s picture

sirprize commented
Status: Closed (fixed) » Active

Problem occurs again with postgres 8.3: The concat function ist not created because int_unsigned seems to no longer exist. With

CREATE OR REPLACE FUNCTION "concat"(integer, character varying) RETURNS character varying AS
'SELECT $1 || $2 ;'
LANGUAGE 'sql';

(int_unsigned replaced by integer) it works again.

lukeprentice’s picture

lukeprentice commented

creating that function in my postgres 8.3.4 worked, but that shouldn't be necessary for drupal. i'd suggest not using mysql-specific SQL syntax. always check with other RDBMs.

CREATE OR REPLACE FUNCTION "concat"(integer, character varying) RETURNS character varying AS
'SELECT $1 || $2 ;'
LANGUAGE 'sql';
dave reid’s picture

dave reid
he/him
Primary language English
Location Nebraska USA
commented
Status: Active » Closed (duplicate)

Duplicate of #229051: Top Visitor Page problem with PostgreSQL 8.3. Should backport when fixed.