views_handler_arg_taxletter() not compatible with PostgreSQL [#157524]

The following line:

$fieldinfo['field'] = ($len <= 0 ? "term_data.name" : "LEFT(term_data.name, $len)");

is causing error report when Drupal is working on PostgreSQL. Due to PostgreSQL documentation, there is no LEFT() function there.

I am trying to prepare the patch for this, but I am not sure if I be albe to correct it in portable way.

According to , the substr() fuction should be better - it is supported by both database engines.

So the line propably should look like:
$fieldinfo['field'] = ($len <= 0 ? "term_data.name" : "substr(term_data.name, 1, $len)");

Comments

Comment #1

merlinofchaos commented 14 July 2007 at 21:33

I'm targetting fixing this for Views 1.7; I played with SUBSTR() but it doesn't work on MySQL 3.23 so we need a more robust solution than just using SUBSTR. Probably a db type check, and probably a Views function to make it easier.

Comment #2

manveru commented 16 July 2007 at 19:58

MySQL 3 is hard to support these days, it is no longer maintained by MySQL AB, I hope in Drupal 7 there will no longer be support for this version of MySQL.

I've changed both calls to left() in sql queries to substr() manually and I've got yet another error reported:

pg_query() [function.pg-query]: Query failed: ERROR: column "term_data.tid" must appear in the GROUP BY clause or be used in an aggregate function

I am not sure what I should do with this error.

query: pager_query SELECT term_data.tid, term_data.name AS letter, count(node.nid) AS num_nodes, term_data.weight AS term_data_weight, term_data.name AS term_data_name, node.sticky AS node_sticky, node.created AS node_created_created FROM drupal_node node LEFT JOIN drupal_term_node term_node ON node.nid = term_node.nid LEFT JOIN drupal_term_data term_data ON term_node.tid = term_data.tid LEFT JOIN drupal_term_data term_data2 ON term_node.tid = term_data2.tid LEFT JOIN drupal_localizernode loc ON loc.nid=node.nid WHERE (loc.locale IN (&#039;pl&#039;,&#039;-&#039;) OR loc.locale IS NULL) AND ( (node.status = &#039;1&#039;) AND (substr(term_data.name, 1, 1) = &#039;S&#039;) AND (term_data.name IS NOT NULL) ) GROUP BY term_data.name, term_data_weight, term_data_name, node_sticky, node_created_created ORDER BY term_data_weight ASC, term_data_name ASC, node_sticky DESC, node_created_created DESC LIMIT 50 OFFSET 0

Comment #3

manveru commented 10 November 2007 at 14:25

Version:	5.x-1.6-beta5	» 5.x-1.6
Priority:	Normal	» Critical

Because problem is still active in 1.6 and only God know when 1.7 will be released I suggest to add PostgreSQL function:

CREATE FUNCTION "left" (IN string character varying, IN length integer) RETURNS character varying AS 'select substring($1 from 0 for $2);' LANGUAGE "sql"
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER

That piece of SQL code in my PostgreSQL database solves problem with Views module and its incompability with this really jazzy database.