Postgresql function "if" is defined to return a boolean value, yet forum requests an integer - last_comment_uid.

user error:
query: SELECT n.nid, l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid FROM node n, node_comment_statistics l /*! USE INDEX (node_comment_timestamp) */, users cu, term_node r WHERE n.nid = r.nid AND r.tid = 1 AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid ORDER BY l.last_comment_timestamp DESC LIMIT 1 OFFSET 0 in /var/www/hosts/www.ambigc.com/htdocs/includes/database.pgsql.inc on line 62.

warning: pg_query() [function.pg-query]: Query failed: ERROR: function if(integer, character varying, character varying) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts. in /var/www/hosts/www.ambigc.com/htdocs/includes/database.pgsql.inc on line 45.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

patrickslee’s picture

Assigned: Unassigned » patrickslee
FileSize
2.69 KB

I have also encountered this bug and made a possible patch by replacing IF statements to CASE.
A patch file is attached.

adrian’s picture

There is an IF function defined in the database.pgsql function. On what page is this happening, as what might be happening is that the type isn't being picked up correctly.

It's defined as IF(anyelement, anyelement, text) returns anyelement. IIRC. What I have seen happen is that it wants the text field to return integer, or vice versa.

patrickslee’s picture

I found the definition in database.pgsql. It is like this:

CREATE FUNCTION "if"(boolean, anyelement, anyelement) RETURNS anyelement AS '
SELECT CASE WHEN $1 THEN $2 ELSE $3 END;
' LANGUAGE 'sql';

So it is actually wanting boolean.

patrickslee’s picture

So my patch will work, and we can make it easier by replacing the IF function to:

IF(l.last_comment_uid <> 0 AND l.last_comment_uid <> NULL, cu.name, l.last_comment_name)

BTW there is another place in forum.module use IF like this.

patrickslee’s picture

Oops... my <>s are stolen.

IF(l.last_comment_uid <> 0 AND l.last_comment_uid <> NULL, cu.name, l.last_comment_name)
cchamilt’s picture

Yes that line works great.
Thanks for your help!

patrickslee’s picture

This patch shows another problem.
The last post user will always show "Anonymous" or whatever you defined.
Looking for solution...

patrickslee’s picture

Use this line instead:

IF(l.last_comment_uid <> 0 AND l.last_comment_uid <> NULL, cu.name, l.last_comment_name)

Don't forget there are two places in forum.module need to be changed.

patrickslee’s picture

Oh what have I done! I changed nothing!

IF(l.last_comment_uid <> 0 AND l.last_comment_uid IS NOT NULL, cu.name, l.last_comment_name)
adrian’s picture

perhaps we could also just add more wrapper functions for the specific types ?

create or replace function IF (integer, anyelement, anyelement) returns anyelement as '
SELECT if (($1 <> 0) AND NOT nullvalue($1), $2, $3);
' LANGUAGE 'sql';

Or something.

plj’s picture

'<>' was missing. So what adrian actually means is this:

CREATE OR REPLACE FUNCTION IF (integer, anyelement, anyelement) RETURNS anyelement AS '
SELECT if (($1 <> 0) AND NOT nullvalue($1), $2, $3);
' LANGUAGE 'sql';

I applied that and now forums work -- no module patches. PostgreSQL 7.4.7.

plj’s picture

Sheesh. I meant that '<>' was missing. Gotta learn to use that preview button...

patrickslee’s picture

Is nullvalue() a function? I cannot find it in neither postgres manual nor mysql manual.
Use "IS NOT NULL" would be better as it is more standard-compatible.
SO we have this:

CREATE OR REPLACE FUNCTION IF (integer, anyelement, anyelement) RETURNS anyelement AS '
SELECT IF(($1 <> 0) AND $1 IS NOT NULL, $2, $3);
' LANGUAGE 'sql';

Looks we have a satisfying fix here. Is this fix going to be committed in CVS for next release?

adrian’s picture

hostmaster=# \df nullvalue
List of functions
Result data type | Schema | Name | Argument data types
------------------+------------+-----------+---------------------
boolean | pg_catalog | nullvalue | "any"

But it doesn't matter to me =)

Dries’s picture

Can you guys provide a patch? I don't have a PostgreSQL setup to test it against. Will commit to DRUPAL-4-6 and HEAD.

patrickslee’s picture

FileSize
363 bytes

Here you have it.

igrcic’s picture

Dont know about you guys but that patch doesnt work for me in drupal 4.6.1!? Im using PostgreSQL.
Instead im using following function that works just fine

if(integer,text,text)

BEGIN
IF $1 THEN
RETURN $2;
END IF;
IF NOT $1 THEN
RETURN $3;
END IF;
END;

igrcic’s picture

Title: Forum module's use of if in Postgresql » Forget previous comment :)
igrcic’s picture

Title: Forget previous comment :) » if postgresql function
Dries’s picture

Just so you know: I'm not committing this patch yet because one user commented that it didn't work. Waiting for a follow-up from the PostgreSQL front.

igrcic’s picture

Ok, here how it is now. First of all, forget my previous if FUNCTION :)

Currenty im using drupal 4.6.1 and I had that problems with forums. Now Im using two if functions, because one is required for forums and I forgot for another another one, but I know that it was called because of different function parameters (bool, int).

So one for the forum is :

CREATE OR REPLACE FUNCTION "if"(integer, text, text) RETURNS text AS '
BEGIN
IF $1 IS NOT NULL AND $1 > 0 THEN
RETURN $2;
ELSE
RETURN $3;
END IF;
END;
' LANGUAGE 'plpgsql';

The another one is standard function that comes with drupal database.inc.pgsql file

CREATE FUNCTION "if"(boolean, anyelement, anyelement) RETURNS anyelement AS '
  SELECT CASE WHEN $1 THEN $2 ELSE $3 END;
' LANGUAGE 'sql';

Im using it for a while and everything works nice!

Before I posted this topic I replaced function with that one in
patch and it worked nice with this one too. But I know that once I had some error with it (didn't write it down grrr), so im stickin with the first function, because it work perfectly for me!

And we have to have 2 functions, one for boolean and one for integer! In mysql it isnt so.

Hope everything's clear now!

igrcic’s picture

Oops, maybe I screwed up something with formatting my post :( Hope you can fix it, sorry

Arto’s picture

Drupal 4.6.1 on PostgreSQL 7.4.7:

The patch (database.pgsql_3.patch) fixes the problem; igrcic's method would certainly fix it as well.

Advocate committing the patch.

plj’s picture

Version: 4.6.0 » x.y.z

I'm advocating it too. Just tested the fix in comment #13 against HEAD. Works well.

I suggest that Dries would just commit it; at least the situation improves dramatically, because as now, the failure is certain.

Perhaps there is something wrong in igric's setup, if the patch does not work? What is your Postgres version?

Cvbge’s picture

I think this bug does not exist anymore. Can you tell me how to reproduce it?

Cvbge’s picture

Status: Needs review » Fixed

Marking as fixed. The IF() construct should be changed to CASE anyway.

Dries’s picture

Status: Fixed » Closed (fixed)