warning: pg_query(): Query failed

[Aaron Steele]

hi,

i'm setting up a forum that's connected to postgresql 7.4.2 (via
drupal.org framework) and i'm getting some errors. here's the link,
followed by the errors verbatim:
[url]http://128.32.146.140/dmap/?q=forum[/url]

warning: pg_query(): Query failed: ERROR: invalid input syntax for type
boolean: "2"
CONTEXT: PL/pgSQL function "if" line 2 at if in
/Library/WebServer/Documents/dmap/includes/database.pgsql.inc on line
104.

user error:
query: SELECT DISTINCT(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 AND '1' ORDER BY l.last_comment_timestamp DESC LIMIT 1 OFFSET 0
in /Library/WebServer/Documents/dmap/includes/database.pgsql.inc on
line 121.

any ideas?

here's my system:
- drupal v4.5.1
- mac os x server v10.3.5
- postgresql v7.4.2
- php v4.3.6

thanks,
aaron


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Steven Klassen]

# i'm setting up a forum that's connected to postgresql 7.4.2 (via
# drupal.org framework) and i'm getting some errors. here's the link,
# followed by the errors verbatim: [url]http://128.32.146.140/dmap/?q=forum[/url]

We would have to see database.pgsql.inc to tell you what's wrong with
it.

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - [url]http://www.commandprompt.com/[/url]
PostgreSQL Replication & Support Services, (503) 667-4564

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])

[Bruno Wolff III]

On Wed, Jan 05, 2005 at 10:17:39 -0800,
Aaron Steele <asteele@berkeley.edu> wrote:

> hi,
>
> warning: pg_query(): Query failed: ERROR: invalid input syntax for type
> boolean: "2"
> CONTEXT: PL/pgSQL function "if" line 2 at if in
> /Library/WebServer/Documents/dmap/includes/database.pgsql.inc on line
> 104.
>
> any ideas?

The actual problem is in the PLSQL function named IF that you didn't show
us.

Recent versions of postgres have tightened up casting to boolean.
Perhaps in the past '2'::boolean might have worked, but in 7.4 this
won't work. '1' or '0' are valid boolean representations.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Aaron Steele]

hi bruno,

would it be useful to see the IF PLSQL function, or would you recommend
a modification to the database.pgsql.inc file?

On Jan 5, 2005, at 10:52 AM, Bruno Wolff III wrote:

> On Wed, Jan 05, 2005 at 10:17:39 -0800,
> Aaron Steele <asteele@berkeley.edu> wrote:

>> hi,
>>
>> warning: pg_query(): Query failed: ERROR: invalid input syntax for
>> type
>> boolean: "2"
>> CONTEXT: PL/pgSQL function "if" line 2 at if in
>> /Library/WebServer/Documents/dmap/includes/database.pgsql.inc on line
>> 104.
>>
>> any ideas?

>
> The actual problem is in the PLSQL function named IF that you didn't
> show
> us.
>
> Recent versions of postgres have tightened up casting to boolean.
> Perhaps in the past '2'::boolean might have worked, but in 7.4 this
> won't work. '1' or '0' are valid boolean representations.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Aaron Steele]

hi steven,

here's another file where the actual SQL query (the one in the error
message) is defined. it's in the forum_get_forums() function. bruno
noticed that the IF (via pl/pgsql) might not like the boolean casting
to "2" since 7.4 is more strict about casting? does this help?

<?php
// $Id: forum.module,v 1.205.2.3 2004/11/06 12:01:39 dries Exp $

/**
* @file
* Enable threaded discussions about general topics.
*/

/**
* Implementation of hook_help().
*/
function forum_help($section) {
switch ($section) {
case 'admin/help#forum':
return t("
<h3>Creating a forum</h3>
<p>The forum module uses taxonomy to organize itself. To create a
forum you first have to create a <a href=\"%taxonomy\">taxonomy
vocabulary</a>. When doing this, choose a sensible name for it (such as
\"fora\") and make sure under \"Types\" that \"forum\" is selected.
Once you have done this, <a href=\"%taxo-terms\">add some terms</a> to
it. Each term will become a forum. If you fill in the description
field, users will be given additional information about the forum on
the main forum page. For example: \"troubleshooting\" - \"Please ask
your questions here.\"</p>
<p>When you are happy with your vocabulary, go to <a
href=\"%forums\">administer &raquo; settings &raquo; forum</a> and set
<strong>Forum vocabulary</strong> to the one you have just created.
There will now be fora active on the site. For users to access them
they must have the \"access content\" <a
href=\"%permission\">permission</a> and to create a topic they must
have the \"create forum topics\" <a
href=\"%permission\">permission</a>. These permissions can be set in
the <a href=\"%permission\">permission</a> pages.</p>
<h4>Icons</h4>
<p>To disable icons, set the icon path as blank in <a
href=\"%forums\">administer &raquo; settings &raquo; forum</a>.</p>
<p>All files in the icon directory are assumed to be images. You
may use images of whatever size you wish, but it is recommended to use
15x15 or 16x16.</p>", array("%taxonomy" =>
url('admin/taxonomy/add/vocabulary'), '%taxo-terms' =>
url('admin/taxonomy'), '%forums' => url('admin/settings/forum'),
'%permission' => url('admin/user/configure/permission')));
case 'admin/modules#description':
return t('Enable threaded discussions about general topics.');
case 'admin/settings/forum':
return t("Forums are threaded discussions based on the taxonomy
system. For the forums to work, the taxonomy module has to be
installed and enabled. When activated, a taxonomy vocabulary (eg.
\"forums\") needs to be <a href=\"%created\">created</a> and bound to
the node type \"forum topic\".", array('%created' =>
url('admin/taxonomy/add/vocabulary')));
case 'node/add#forum':
return t('A forum is a threaded discussion, enabling users to
communicate about a particular topic.');
}

// The 'add forum topic' form takes a variable argument:
if (substr($section, 0, 14) == 'node/add/forum'){
return variable_get('forum_help', '');
}
}

/**
* Implementation of hook_node_name().
*/
function forum_node_name($node) {
return t('forum topic');
}

/**
* Implementation of hook_access().
*/
function forum_access($op, $node) {
if ($op == 'create') {
return user_access('create forum topics');
}
}

/**
* Implementation of hook_perm().
*/
function forum_perm() {
return array('create forum topics');
}

/**
* Implementation of hook_settings().
*/
function forum_settings() {

if (module_exist('taxonomy')) {
$vocs[0] = '<'. t('none') .'>';
foreach (taxonomy_get_vocabularies('forum') as $vid => $voc) {
$vocs[$vid] = $voc->name;
}

if ($voc) {
$group = form_select(t('Forum vocabulary'),
'forum_nav_vocabulary', variable_get('forum_nav_vocabulary', ''),
$vocs, t("The taxonomy vocabulary that will be used as the navigation
tree. The vocabulary's terms define the forums."));
$group .= _taxonomy_term_select(t('Containers'),
'forum_containers', variable_get('forum_containers', array()),
variable_get('forum_nav_vocabulary', ''), t('You can choose forums
which will not have topics, but will be just containers for other
forums. This lets you both group and nest forums.'), 1, '<'. t('none')
..'>');
$output = form_group(t('Forum structure settings'), $group);

$group = form_textarea(t('Explanation or submission
guidelines'), 'forum_help', variable_get('forum_help', ''), 70, 5,
t('This text will be displayed at the top of the forum submission form.
It is useful for helping or instructing your users.'));
$group .= form_textfield(t('Forum icon path'), 'forum_icon_path',
variable_get('forum_icon_path', ''), 30, 255, t('The path to the forum
icons. Leave blank to disable icons. Don\'t add a trailing slash.
Default icons are available in the "misc" directory.'));
$group .= form_select(t('Hot topic threshold'),
'forum_hot_topic', variable_get('forum_hot_topic', 15),
drupal_map_assoc(array(5, 10, 15, 20, 25, 30, 35, 40, 50, 60, 80, 100,
10000)), t('The number of posts a topic must have to be considered
<strong>hot</strong>.'));
$group .= form_select(t('Topics per page'), 'forum_per_page',
variable_get('forum_per_page', 25), drupal_map_assoc(array(10, 25, 50,
75, 100)), t('The default number of topics displayed per page; links to
browse older messages are automatically being displayed.'));
$group .= form_radios(t('Default order'), 'forum_order',
variable_get('forum_order', '1'), array(1 => t('Date - newest first'),
2 => t('Date - oldest first'), 3 => t('Posts - most active first'), 4=>
t('Posts - least active first')), t('The default display order for
topics.'));
$output .= form_group(t('Forum viewing options'), $group);

$group = form_select(t('Number of topics in block'),
'forum_block_num', variable_get('forum_block_num', '5'),
drupal_map_assoc(array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
16, 17, 18, 19, 20)), t('The number of topics to show in the "Forum
topics" block. To enable the block, go to the <a
href="%block-administration">block administration</a> page.',
array('%block-administration' => url('admin/block'))));
$output .= form_group(t('"Forum topic" block settings'), $group);
}
}

return $output;
}

/**
* Implementation of hook_taxonomy().
*/
function forum_taxonomy($op, $type, $object) {
if ($type == 'vocabulary' && ($op == 'insert' || $op == 'update')) {
if (variable_get('forum_nav_vocabulary', '') == '' &&
in_array('forum', $object['nodes'])) {
// since none is already set, silently set this vocabulary as the
navigation vocabulary
variable_set('forum_nav_vocabulary', $object['vid']);
}
}
}

/**
* Implementation of hook_load().
*/
function forum_load($node) {
$forum = db_fetch_object(db_query('SELECT * FROM {forum} WHERE nid =
%d', $node->nid));

return $forum;
}

/**
* Implementation of hook_block().
*
* Generates a block containing the currently active forum topics and
the
* most recently added forum topics.
*/
function forum_block($op = 'list', $delta = 0) {

if ($op == 'list') {
$blocks[0]['info'] = t('Forum topics');
}
else {
if (user_access('access content')) {
$content = node_title_list(db_query_range("SELECT
DISTINCT(n.nid), n.title, l.last_comment_timestamp FROM {node} n INNER
JOIN {node_comment_statistics} l ON n.nid = l.nid ".
node_access_join_sql() ." WHERE n.status = 1 AND n.type='forum' AND ".
node_access_where_sql() ." ORDER BY l.last_comment_timestamp DESC", 0,
variable_get('forum_block_num', '5')), t('Active forum topics:'));

$content .= node_title_list(db_query_range("SELECT
DISTINCT(n.nid), n.title FROM {node} n ". node_access_join_sql() ."
WHERE n.type = 'forum' AND n.status = 1 AND ". node_access_where_sql()
.." ORDER BY n.nid DESC", 0, variable_get('forum_block_num', '5')),
t('New forum topics:'));

if ($content) {
$content .= '<div class="more-link">'. l(t('more'), 'forum',
array('title' => t('Read the latest forum topics.'))) .'</div>';
}

$blocks['subject'] = t('Forum topics');
$blocks['content'] = $content;
}
}

return $blocks;
}

/**
* Implementation of hook_link().
*/
function forum_link($type, $node = 0, $main = 0) {
global $user;

$links = array();

if ($type == 'page' && user_access('access content')) {
$links[] = l(t('forums'), 'forum');
}

if (!$main && $type == 'node' && $node->type == 'forum') {
// get previous and next topic

$result = db_query("SELECT DISTINCT(n.nid), n.title, n.sticky,
l.comment_count, l.last_comment_timestamp FROM {node} n INNER JOIN
{node_comment_statistics} l ON n.nid = l.nid " . node_access_join_sql()
.. " INNER JOIN {term_node} r ON n.nid = r.nid AND r.tid = %d WHERE
n.status = 1 AND n.type='forum' AND " . node_access_where_sql() . '
ORDER BY n.sticky DESC, '.
_forum_get_topic_order_sql(variable_get('forum_ord er', 1)),
$node->tid);

while ($topic = db_fetch_object($result)) {
if ($stop == 1) {
$next = new StdClass();
$next->nid = $topic->nid;
$next->title = $topic->title;
break;
}
if ($topic->nid == $node->nid) {
$stop = 1;
}
else {
$prev->nid = $topic->nid;
$prev->title = $topic->title;
}
}

if ($prev) {
$links[] = l(t('previous forum topic'), "node/$prev->nid",
array('title' => $prev->title));
}

if ($next) {
$links[] = l(t('next forum topic'), "node/$next->nid",
array('title' => $next->title));
}
}

return $links;
}

/**
* Implementation of hook_menu().
*/
function forum_menu($may_cache) {
$items = array();

if ($may_cache) {
$items[] = array('path' => 'node/add/forum', 'title' => t('forum
topic'),
'access' => user_access('create forum topics'));
$items[] = array('path' => 'forum', 'title' => t('forums'),
'callback' => 'forum_page',
'access' => user_access('access content'),
'type' => MENU_CALLBACK);
}

return $items;
}

/**
* Implementation of hook_content().
*/
function forum_content($node, $teaser = FALSE) {
return node_prepare($node, $teaser);
}

/**
* Implementation of hook_view().
*/
function forum_view(&$node, $teaser = FALSE, $page = FALSE) {

if ($page) {
$vocabulary =
taxonomy_get_vocabulary(variable_get('forum_nav_vo cabulary', ''));
// Breadcrumb navigation
$breadcrumb = array();
$breadcrumb[] = array('path' => 'forum', 'title' =>
$vocabulary->name);
if ($parents = taxonomy_get_parents_all($node->tid)) {
$parents = array_reverse($parents);
foreach ($parents as $p) {
$breadcrumb[] = array('path' => 'forum/'. $p->tid, 'title' =>
$p->name);
}
}
$breadcrumb[] = array('path' => 'node/'. $node->nid);
menu_set_location($breadcrumb);
}

$node = forum_content($node, $teaser);
}

/**
* Implementation of hook_validate().
*
* Check in particular that only a "leaf" term in the associated
taxonomy
* vocabulary is selected, not a "container" term.
*/
function forum_validate(&$node) {
// Make sure all fields are set properly:
$node->icon = $node->icon ? $node->icon : '';

if ($node->taxonomy) {
// Extract the node's proper topic ID.
$vocabulary = variable_get('forum_nav_vocabulary', '');
$containers = variable_get('forum_containers', array());
foreach ($node->taxonomy as $term) {
if (db_result(db_query('SELECT COUNT(*) FROM {term_data} WHERE
tid = %d AND vid = %d', $term, $vocabulary))) {
if (in_array($term, $containers)) {
$term = taxonomy_get_term($term);
form_set_error('taxonomy', t('The item %forum is only a
container for forums. Please select one of the forums below it.',
array('%forum' => "<em>$term->name</em>")));
}
else {
$node->tid = $term;
}
}
}
if ($node->tid && $node->shadow) {
$terms = array_keys(taxonomy_node_get_terms($node->nid));
if (!in_array($node->tid, $terms)) {
$terms[] = $node->tid;
}
$node->taxonomy = $terms;
}
}
}

/**
* Implementation of hook_update().
*/
function forum_update($node) {
db_query('UPDATE {forum} SET tid = %d WHERE nid = %d', $node->tid,
$node->nid);
}

/**
* Implementation of hook_form().
*/
function forum_form(&$node) {
if (!$node->nid) {
// new topic
$node->taxonomy[] = arg(3);
}
else {
$node->taxonomy = array($node->tid);
}

$output = implode('', taxonomy_node_form('forum', $node));

if ($node->nid) {
// if editing, give option to leave shadows
$shadow = (count(taxonomy_node_get_terms($node->nid)) > 1);
$output .= form_checkbox(t('Leave shadow copy'), 'shadow', 1,
$shadow, t('If you move this topic, you can leave a link in the old
forum to the new forum.'));
}

$output .= form_textarea(t('Body'), 'body', $node->body, 60, 20, '');
$output .= filter_form('format', $node->format);

return $output;
}

/**
* Implementation of hook_insert().
*/
function forum_insert($node) {
db_query('INSERT INTO {forum} (nid, tid) VALUES (%d, %d)',
$node->nid, $node->tid);
}

/**
* Implementation of hook_delete().
*/
function forum_delete(&$node) {
db_query('DELETE FROM {forum} WHERE nid = %d', $node->nid);
}

/**
* Formats a topic for display
*
* @TODO Give a better description. Not sure where this function is
used yet.
*/
function _forum_format($topic) {
if ($topic && $topic->timestamp) {
return t('%time ago<br />by %author', array('%time' =>
format_interval(time() - $topic->timestamp), '%author' =>
format_name($topic)));
}
else {
return message_na();
}
}

/**
* Returns a list of all forums for a given taxonomy id
*
* Forum objects contain the following fields
* -num_topics Number of topics in the forum
* -num_posts Total number of posts in all topics
* -last_post Most recent post for the forum
*
* @param $tid
* Taxonomy ID of the vocabulary that holds the forum list.
* @return
* Array of object containing the forum information.
*/
function forum_get_forums($tid = 0) {
if (!$tid) {
$tid = 0;
}

$forums = array();
$_forums = taxonomy_get_tree(variable_get('forum_nav_vocabula ry',
''), $tid);

if (count($_forums)) {

$counts = array();

$_counts = db_query("SELECT r.tid, COUNT(n.nid) AS topic_count,
SUM(l.comment_count) AS comment_count FROM {node} n INNER JOIN
{node_comment_statistics} l ON n.nid = l.nid INNER JOIN {term_node} r
ON n.nid = r.nid " . node_access_join_sql() . " WHERE n.status = 1 AND
n.type = 'forum' AND " . node_access_where_sql() . " GROUP BY r.tid",
$forum->tid);
while ($count = db_fetch_object($_counts)) {
$counts[$count->tid] = $count;
}
}

foreach ($_forums as $forum) {
if (in_array($forum->tid, variable_get('forum_containers',
array()))) {
$forum->container = 1;
}

if ($counts[$forum->tid]) {
$forum->num_topics = $counts[$forum->tid]->topic_count;
$forum->num_posts = $counts[$forum->tid]->topic_count +
$counts[$forum->tid]->comment_count;
}
else {
$forum->num_topics = 0;
$forum->num_posts = 0;
}

// This query does not use full ANSI syntax since MySQL 3.x does
not support
// table1 INNER JOIN table2 INNER JOIN table3 ON table2_criteria ON
table3_criteria
// used to join node_comment_statistics to users
$topic = db_fetch_object(db_query_range('SELECT DISTINCT(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_access_join_sql() . ", {node_comment_statistics} l
/*! USE INDEX (node_comment_timestamp) */, {users} cu, {term_node} r
WHERE n.nid = r.nid AND r.tid = %d AND n.status = 1 AND n.type =
'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND " .
node_access_where_sql() . ' ORDER BY l.last_comment_timestamp DESC',
$forum->tid, 0, 1));
$last_post->timestamp = $topic->last_comment_timestamp;
$last_post->name = $topic->last_comment_name;
$last_post->uid = $topic->last_comment_uid;
$forum->last_post = $last_post;

$forums[$forum->tid] = $forum;
}

return $forums;
}

function _forum_topics_read($term, $uid) {
// Calculate the number of topics the user has read. Assume all
entries older
// than NODE_NEW_LIMIT are read, and include the recent posts that
user has
// read.
$ancient = db_result(db_query("SELECT COUNT(n.nid) FROM {node} n
INNER JOIN {term_node} r ON n.nid = r.nid AND r.tid = %d ".
node_access_join_sql() ." WHERE n.created <= %d AND n.status = 1 AND
n.type = 'forum' AND ". node_access_where_sql(), $term,
NODE_NEW_LIMIT));
$recent = db_result(db_query("SELECT COUNT(n.nid) FROM {node} n ".
node_access_join_sql() ." INNER JOIN {history} h ON n.nid = h.nid AND
h.uid = %d INNER JOIN {term_node} r ON n.nid = r.nid AND r.tid = %d
WHERE n.status = 1 AND n.type = 'forum' AND n.created > %d AND ".
node_access_where_sql(), $uid, $term, NODE_NEW_LIMIT));

return $ancient + $recent;
}

function forum_get_topics($tid, $sortby, $forum_per_page) {
global $user, $forum_topic_list_header;

$forum_topic_list_header = array(
array('data' => '&nbsp;'),
array('data' => t('Topic'), 'field' => 'n.title'),
array('data' => t('Replies'), 'field' => 'l.comment_count'),
array('data' => t('Created'), 'field' => 'n.created'),
array('data' => t('Last reply'), 'field' =>
'l.last_comment_timestamp'),
);

$order = _forum_get_topic_order($sortby);
for ($i = 0; $i < count($forum_topic_list_header); $i++) {
if ($forum_topic_list_header[$i]['field'] == $order['field']) {
$forum_topic_list_header[$i]['sort'] = $order['sort'];
}
}

$term = taxonomy_get_term($tid);
$check_tid = $tid ? "'". check_query($tid) ."'" : 'NULL';

$sql = "SELECT DISTINCT(n.nid), f.tid, n.title, n.sticky, u.name,
u.uid, n.created AS timestamp, n.comment AS comment_mode,
l.last_comment_timestamp, IF(l.last_comment_uid, cu.name,
l.last_comment_name) as last_comment_name, l.last_comment_uid,
l.comment_count AS num_comments FROM {node} n ". node_access_join_sql()
..", {node_comment_statistics} l, {users} cu, {term_node} r, {users} u,
{forum} f WHERE n.status = 1 AND l.last_comment_uid = cu.uid AND n.nid
= l.nid AND n.nid = r.nid AND r.tid = $check_tid AND n.uid = u.uid AND
n.nid = f.nid AND ". node_access_where_sql();
$sql .= tablesort_sql($forum_topic_list_header, 'n.sticky DESC,');

$sql_count = "SELECT COUNT(DISTINCT(n.nid)) FROM {node} n ".
node_access_join_sql() ." INNER JOIN {term_node} r ON n.nid = r.nid AND
r.tid = $check_tid WHERE n.status = 1 AND n.type = 'forum' AND ".
node_access_where_sql();

$result = pager_query($sql, $forum_per_page, 0, $sql_count);

while ($topic = db_fetch_object($result)) {
if ($user->uid) {
// folder is new if topic is new or there are new comments since
last visit
if ($topic->tid != $tid) {
$topic->new = 0;
}
else {
$history = _forum_user_last_visit($topic->nid);
$topic->new_replies = comment_num_new($topic->nid, $history);
$topic->new = $topic->new_replies || ($topic->timestamp >
$history);
}
}
else {
// Do not track "new replies" status for topics if the user is
anonymous.
$topic->new_replies = 0;
$topic->new = 0;
}

if ($topic->num_comments > 0) {
$last_reply->timestamp = $topic->last_comment_timestamp;
$last_reply->name = $topic->last_comment_name;
$last_reply->uid = $topic->last_comment_uid;
$topic->last_reply = $last_reply;
}
$topics[] = $topic;
}

return $topics;
}

/**
* Finds the first unread node for a given forum.
*/
function _forum_new($tid) {
global $user;

$nid = db_result(db_query_range("SELECT DISTINCT(n.nid) FROM {node} n
LEFT JOIN {history} h ON n.nid = h.nid AND h.uid = %d INNER JOIN
{term_node} r ON n.nid = r.nid AND r.tid = %d " .
node_access_join_sql() . " WHERE n.status = 1 AND n.type = 'forum' AND
h.nid IS NULL AND n.created > %d AND " . node_access_where_sql() . "
ORDER BY created", $user->uid, $tid, NODE_NEW_LIMIT, 0, 1));

return $nid ? $nid : 0;
}

/**
* Menu callback; prints a forum listing.
*/
function forum_page($tid = 0, $display = 'all') {
global $user;

if (module_exist('taxonomy')) {
if ($display == 'new') {
if ($nid = _forum_new($tid)) {
drupal_goto("node/$nid");
}
}
else {
$forum_per_page = variable_get('forum_per_page', 25);
$sortby = variable_get('forum_order', 1);

$forums = forum_get_forums($tid);
$parents = taxonomy_get_parents_all($tid);
if ($tid && !in_array($tid, variable_get('forum_containers',
array()))) {
$topics = forum_get_topics($tid, $sortby, $forum_per_page);
}

print theme('forum_display', $forums, $topics, $parents, $tid,
$sortby, $forum_per_page);
}
}
else {
print theme('page', forum_help('admin/settings/forum'),
t('Warning'));
}
}

/**
* Format the forum body.
*
* @ingroup themeable
*/
function theme_forum_display($forums, $topics, $parents, $tid, $sortby,
$forum_per_page) {
global $user;
// forum list, topics list, topic browser and 'add new topic' link

$vocabulary =
taxonomy_get_vocabulary(variable_get('forum_nav_vo cabulary', ''));
$title = $vocabulary->name;

// Breadcrumb navigation:
$breadcrumb = array();
if ($tid) {
$breadcrumb[] = array('path' => 'forum', 'title' => $title);
}

if ($parents) {
$parents = array_reverse($parents);
foreach ($parents as $p) {
if ($p->tid == $tid) {
$title = $p->name;
}
else {
$breadcrumb[] = array('path' => 'forum/'. $p->tid, 'title' =>
$p->name);
}
}
}
$breadcrumb[] = array('path' => $_GET['q']);
menu_set_location($breadcrumb);

if (count($forums) || count($parents)) {
$output = '<div id="forum">';
$output .= '<ul>';

if (module_exist('tracker')) {
if ($user->uid) {
$output .= ' <li>'. l(t('My forum discussions.'),
"tracker/$user->uid") .'</li>';
}

$output .= ' <li>'. l(t('Active forum discussions.'), 'tracker')
..'</li>';
}

if (user_access('create forum topics')) {
$output .= '<li>'. l(t('Post new forum topic.'),
"node/add/forum/$tid") .'</li>';
}
else if ($user->uid) {
$output .= '<li>'. t('You are not allowed to post a new forum
topic.') .'</li>';
}
else {
$output .= '<li>'. t('<a href="%login">Login</a> to post a new
forum topic.', array('%login' => url('user/login'))) .'</li>';
}
$output .= '</ul>';

$output .= theme('forum_list', $forums, $parents, $tid);

if ($tid && !in_array($tid, variable_get('forum_containers',
array()))) {
$output .= theme('forum_topic_list', $tid, $topics, $sortby,
$forum_per_page);
}
$output .= '</div>';
}
else {
$title = t('No forums defined');
$output = '';
}

print theme('page', $output, $title);
}

/**
* Format the forum listing.
*
* @ingroup themeable
*/
function theme_forum_list($forums, $parents, $tid) {
global $user;

if ($forums) {

$header = array(t('Forum'), t('Topics'), t('Posts'), t('Last
post'));

foreach ($forums as $forum) {
if ($forum->container) {
$description = '<div style="margin-left: '. ($forum->depth *
30) ."px;\">\n";
$description .= ' <div class="name">'. l($forum->name,
"forum/$forum->tid") ."</div>\n";

if ($forum->description) {
$description .= " <div
class=\"description\">$forum->description</div>\n";
}
$description .= "</div>\n";

$rows[] = array(array('data' => $description, 'class' =>
'container', 'colspan' => 4));
}
else {
$forum->old_topics = _forum_topics_read($forum->tid,
$user->uid);
if ($user->uid) {
$new_topics = $forum->num_topics - $forum->old_topics;
}
else {
$new_topics = 0;
}

$description = '<div style="margin-left: '. ($forum->depth *
30) ."px;\">\n";
$description .= ' <div class="name">'. l($forum->name,
"forum/$forum->tid") ."</div>\n";

if ($forum->description) {
$description .= " <div
class=\"description\">$forum->description</div>\n";
}
$description .= "</div>\n";

$rows[] = array(
array('data' => $description, 'class' => 'forum'),
array('data' => $forum->num_topics . ($new_topics ? '<br />'.
l(t('%a new', array('%a' => $new_topics)), "forum/$forum->tid", NULL,
NULL, 'new') : ''), 'class' => 'topics'),
array('data' => $forum->num_posts, 'class' => 'posts'),
array('data' => _forum_format($forum->last_post), 'class' =>
'last-reply'));
}
}

return theme('table', $header, $rows);

}

}

/**
* Format the topic listing.
*
* @ingroup themeable
*/
function theme_forum_topic_list($tid, $topics, $sortby,
$forum_per_page) {
global $forum_topic_list_header;

if ($topics) {

foreach ($topics as $topic) {
// folder is new if topic is new or there are new comments since
last visit
if ($topic->tid != $tid) {
$rows[] = array(
array('data' => _forum_icon($topic->new,
$topic->num_comments, $topic->comment_mode, $topic->sticky), 'class' =>
'icon'),
array('data' => $topic->title, 'class' => 'title'),
array('data' => l(t('This topic has been moved'),
"forum/$topic->tid"), 'colspan' => '3')
);
}
else {
$rows[] = array(
array('data' => _forum_icon($topic->new,
$topic->num_comments, $topic->comment_mode, $topic->sticky), 'class' =>
'icon'),
array('data' => l($topic->title, "node/$topic->nid"), 'class'
=> 'topic'),
array('data' => $topic->num_comments . ($topic->new_replies ?
'<br />'. l(t('%a new', array('%a' => $topic->new_replies)),
"node/$topic->nid", NULL, NULL, 'new') : ''), 'class' => 'replies'),
array('data' => _forum_format($topic), 'class' => 'created'),
array('data' => _forum_format($topic->last_reply), 'class' =>
'last-reply')
);
}
}

if ($pager = theme('pager', NULL, $forum_per_page, 0,
tablesort_pager())) {
$rows[] = array(array('data' => $pager, 'colspan' => '5', 'class'
=> 'pager'));
}
}

$output .= theme('table', $forum_topic_list_header, $rows);

return $output;
}

function _forum_icon($new_posts, $num_posts = 0, $comment_mode = 0,
$sticky = 0) {

$base_path = variable_get('forum_icon_path', '');
if ($base_path) {
if ($num_posts > variable_get('forum_hot_topic', 15)) {
$icon = $new_posts ? 'hot-new' : 'hot';
}
else {
$icon = $new_posts ? 'new' : 'default';
}

if ($comment_mode == 1) {
$icon = 'closed';
}

if ($sticky == 1) {
$icon = 'sticky';
}

// default
$file = "misc/forum-$icon.png";

$output = theme('image', $file);
}
else {
$output = '&nbsp;';
}

if ($new_posts) {
$output = "<a name=\"new\">$output</a>";
}

return $output;
}

function _forum_user_last_visit($nid) {
global $user;
static $history = array();

if (empty($history)) {
$result = db_query('SELECT nid, timestamp FROM {history} WHERE uid
= %d', $user->uid);
while ($t = db_fetch_object($result)) {
$history[$t->nid] = $t->timestamp > NODE_NEW_LIMIT ?
$t->timestamp : NODE_NEW_LIMIT;
}
}
return $history[$nid] ? $history[$nid] : NODE_NEW_LIMIT;
}

function _forum_get_topic_order($sortby) {
switch ($sortby) {
case 1:
return array('field' => 'l.last_comment_timestamp', 'sort' =>
'desc');
break;
case 2:
return array('field' => 'l.last_comment_timestamp', 'sort' =>
'asc');
break;
case 3:
return array('field' => 'l.comment_count', 'sort' => 'desc');
break;
case 4:
return array('field' => 'l.comment_count', 'sort' => 'asc');
break;
}
}

function _forum_get_topic_order_sql($sortby) {
$order = _forum_get_topic_order($sortby);
return $order['field'] .' '. $order['sort'];
}

?>

On Jan 5, 2005, at 10:32 AM, Steven Klassen wrote:

> # i'm setting up a forum that's connected to postgresql 7.4.2 (via
> # drupal.org framework) and i'm getting some errors. here's the link,
> # followed by the errors verbatim: [url]http://128.32.146.140/dmap/?q=forum[/url]
>
> We would have to see database.pgsql.inc to tell you what's wrong with
> it.
>
> --
> Steven Klassen - Lead Programmer
> Command Prompt, Inc. - [url]http://www.commandprompt.com/[/url]
> PostgreSQL Replication & Support Services, (503) 667-4564
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> [email]majordomo@postgresql.org[/email])
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])

[Bruno Wolff III]

On Wed, Jan 05, 2005 at 11:16:29 -0800,
Aaron Steele <asteele@berkeley.edu> wrote:

>
> would it be useful to see the IF PLSQL function, or would you recommend
> a modification to the database.pgsql.inc file?

You should probably try looking at the IF function first to see if you
can spot it using 2 as a boolean and why it is doing that. It may be
simple to fix.

It didn't look to me like the problem was with the php code unless the code
you showed us was getting changed by php before getting sent to postgres.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

[Bruno Wolff III]

On Thu, Jan 06, 2005 at 14:38:53 -0800,
Aaron Steele <asteele@berkeley.edu> wrote:

> hi bruno,

Please copy replies to the list so that other people can help and learn
from your problem.

>
> here's the IF() from psql, although i'm not familiar enough to really
> know what to look for. do you see anything obviously wrong here? is
> there a better way to look at the IF() function?
> .................................................. ......................
> ......................................
> dmapdb=# \df+ if
>
> List of functions
> Result data type | Schema | Name | Argument data types | Owner |
> Language | Source code
> | Description
> ------------------+--------+------+---------------------+-------
> +----------
> +-----------------------------------------------------------------------
> -----------------------+-------------
> text | public | if | integer, text, text | dmap |
> plpgsql |
> BEGIN
> IF $1 THEN
> RETURN $2;
> END IF;
> IF NOT $1 THEN
> RETURN $3;
> END IF;
> END;

In the php code you showed the following fragment:
IF(l.last_comment_uid, cu.name, l.last_comment_name)

I doubt that l.last_comment_uid is a boolean based on its name.
If it isn't this is probably the source of your problem.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

[Aaron Steele]

hi bruno,

turns out that l.last_comment_uid and l.last_comment_name are integer
and char var respectively. since i'm using 7.4.1 with strict boolean
casting, is there a better alternative to instantiating a different
version of pgsql on my server?

> In the php code you showed the following fragment:
> IF(l.last_comment_uid, cu.name, l.last_comment_name)
>
> I doubt that l.last_comment_uid is a boolean based on its name.
> If it isn't this is probably the source of your problem.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> [email]majordomo@postgresql.org[/email]
>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruno Wolff III]

On Thu, Jan 06, 2005 at 17:32:30 -0800,
Aaron Steele <asteele@berkeley.edu> wrote:

> hi bruno,
>
> turns out that l.last_comment_uid and l.last_comment_name are integer
> and char var respectively. since i'm using 7.4.1 with strict boolean
> casting, is there a better alternative to instantiating a different
> version of pgsql on my server?

A better solution is fixing your code. What do you expect it to do
anyhow? Once you figure out what you want it to do, you should be able
to write a boolean expression that is true, false or null when you
want it to be. If fact from what I saw it seems that you could just use
a CASE expression and skip the function call altogether.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

[url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

[Aaron Steele]

hi bruno,

yeah, the postgresql problem was fixed by changing IF statements to
CASE statements in forum.module code:

// remove:
-- $topic = db_fetch_object(db_query_range('SELECT DISTINCT(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_access_join_sql() . ", {node_comment_statistics} l
/*! USE INDEX (node_comment_timestamp) */, {users} cu, {term_node} r
WHERE n.nid = r.nid AND r.tid = %d AND n.status = 1 AND n.type =
'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND " .
node_access_where_sql() . ' ORDER BY l.last_comment_timestamp DESC',
$forum->tid, 0, 1));

// add:
++ $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid),
l.last_comment_timestamp, CASE WHEN l.last_comment_uid = 1 THEN cu.name
ELSE l.last_comment_name END as last_comment_name, l.last_comment_uid
FROM {node} n ' . node_access_join_sql() . ", {node_comment_statistics}
l, {users} cu, {term_node} r WHERE n.nid = r.nid AND r.tid = %d AND
n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND
n.nid = l.nid AND " . node_access_where_sql() . ' ORDER BY
l.last_comment_timestamp DESC', $forum->tid, 0, 1));

thanks for all the support!

aaron

On Jan 6, 2005, at 10:25 PM, Bruno Wolff III wrote:

> On Thu, Jan 06, 2005 at 17:32:30 -0800,
> Aaron Steele <asteele@berkeley.edu> wrote:

>> hi bruno,
>>
>> turns out that l.last_comment_uid and l.last_comment_name are integer
>> and char var respectively. since i'm using 7.4.1 with strict boolean
>> casting, is there a better alternative to instantiating a different
>> version of pgsql on my server?

>
> A better solution is fixing your code. What do you expect it to do
> anyhow? Once you figure out what you want it to do, you should be able
> to write a boolean expression that is true, false or null when you
> want it to be. If fact from what I saw it seems that you could just use
> a CASE expression and skip the function call altogether.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]
>

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

[url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]