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

OpenACS Home : Forums : .LRN Q&A : Search broken on Postgresql Forums

Posted by Dave Bauer on 08/13/02 03:21 PM

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

2: Response to Search broken on Postgresql Forums (response to 1)

Posted by Dan Wickstrom on 08/13/02 03:25 PM

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.

3: Response to Search broken on Postgresql Forums (response to 1)

Posted by Dave Bauer on 08/13/02 04:10 PM

Ok. this is fixed. I submitted a patch.

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