In database/database.pgsql many of the functions such as greatest(integer, integer) were defined with plpgsql. This patch simply changes the definitions a bit to use regular SQL (SELECT) -- so users won't have to load plpgsql with createlang anymore.

In addition, I have updated the IF(expr1,expr2,expr3) function to reflect MySQL's definition of it. Before expr1 always had to be passed as a boolean, and now it is anyelement. Also now expr1 is true if it's both NOT NULL and <> 0. Anyways, this means that if Drupal modules are passing integers when using the sql IF() function, the query will now work.

NOTE: The IF() function is not ANSI SQL, perhaps in a later patch we can replace all usage of it with CASE?

CommentFileSizeAuthor
#1 INSTALL.txt.patch (removes plpgsql)594 bytesSmirnov
database.pgsql_4.patch1.85 KBSmirnov

Comments

Smirnov’s picture

Smirnov commented
StatusFileSize
new INSTALL.txt.patch (removes plpgsql)594 bytes

If this issue to add installation instructions for PG gets approved and committed then please use this patch to update INSTALL.txt so that it no longer mentions plpgsql.

dries’s picture

dries commented

Waiting for a review from Adrian. I don't have a PostgreSQL setup.

Cvbge’s picture

Cvbge commented

About function 'greatest': according to mysql documentation:

Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL. As of 5.0.13, it returns NULL if any argument is NULL.

Which one should PostgreSQL support? Now it supports < 5.0.13

Also, mysql's greates() can compare any type. Postgresql's version can only compare integer types. I don't know if it is possibile to write function/functions that can compare any type the way mysql does it.

wedge’s picture

wedge commented

Except for the IF function these work ok for me. When I try to access the forum I recieve messages like:
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 /htdocs/includes/database.pgsql.inc on line 69.

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, 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 /htdocs/includes/database.pgsql.inc on line 86.

Running postgresql 8.0.3 and php 5.0.5.

I tried the function given in http://drupal.org/node/11903 but then I recieve this:
Query failed: ERROR: argument of CASE/WHEN must be type boolean, not type integer
CONTEXT: SQL function "if" during inlining in /htdocs/includes/database.pgsql.inc on line 69.

So perhaps it might be a good idea to swith to CASE WHEN sql.

Cvbge’s picture

Cvbge commented
Status: Needs review » Closed (duplicate)

Setting to duplicate of http://drupal.org/node/37383 (although this issue was first)