The Toolkit for Online Communities
18916 Community Members, 0 members online, 1576 visitors today
Log In Register
OpenACS Home : Forums : .LRN Q&A : Search broken on Postgresql Forums

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

Icon of envelope Request notifications

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

            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,
            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
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.
Posted by Dave Bauer on
Ok. this is fixed. I submitted a patch.