Forum .LRN Q&A: Search broken on Postgresql Forums

Collapse
Posted by Dave Bauer on
First: search-postgresql.xql has Oracle listed as the database. After fixing that I get this error:
Database operation "select" failed (exception NSDB, "Query was not a
statement returning rows.")

ERROR:  Attribute 'user_name' not found

SQL: 
            select forums_messages.*,
                   person__name(forums_messages.user_id) as user_name,
                   to_char(forums_messages.posting_date, 'Mon DD YYYY
HH24:MI:SS') as posting_date,
                   100 as the_score
            from forums_messages,
                 forums_forums
            where forums_messages.forum_id = forums_forums.forum_id
            and forums_forums.package_id = '3061'
            and forums_messages.state = 'approved'
            and (    forums_messages.subject like ('%' || 'foo' || '%')
                  or forums_messages.content like ('%' || 'foo' || '%')
                  or user_name like ('%' || 'foo' || '%')
                )
            order by the_score desc,
                     forums_messages.posting_date desc
Which shows that the alias "user_name" to the function "person_name" is not recognized in the where clause. Does anyone know if this is a postgresql specific problem. Possibly it is a version problem. This is running on Postgresql 7.1.3
Collapse
Posted by Dan Wickstrom on
I think that's an SQL92 problem.  Postgresql is following the standard, and oracle is doing its own thing.  You need to repeat the function call in the where clause.
Collapse
Posted by Dave Bauer on
Ok. this is fixed. I submitted a patch.

https://openacs.org/sdm/one-patch.tcl?patch_id=339