Forum OpenACS Q&A: Trying to fix pl-sql.sql/pseudo_contains

Collapse
1: Trying to fix pl-sql.sql/pseudo_contains
Posted by Jim Sims on 10/07/01 12:32 AM
So I was playing around with the ecommerce search widget and
discovered the error where it won't work for two words:
https://openacs.org/sdm/one-baf.tcl?baf_id=898.

I wanted to 'fix' my site, so edited ~/www/doc/sql/pl-sql.sql, then
dropped and created the new pseudo_contains function from psql. (Also
restarted the db and aolserver just to be safe.)

Problem is, I still have the same error: searching on "oneword" works
whereas searching on "two words" craps out.

Using OpenACS 3.2.5 here, PGSQL 7.1.2, AOLServer 3.2.
What am I missing here?

Collapse
2: Response to Trying to fix pl-sql.sql/pseudo_contains (response to 1)
Posted by Dave Bauer on 10/07/01 12:57 AM
Do you get an actual error message or does the search return incorrect results?
Collapse
3: Response to Trying to fix pl-sql.sql/pseudo_contains (response to 1)
Posted by Jim Sims on 10/07/01 01:09 AM
In the browser: Server Error The requested URL cannot be accessed due to a system error on this server. AOLserver/3.2+ad12 on http:...

In /var/log/aolserver/defaultacs.log:

[06/Oct/2001:18:12:07][711.23560][-conn13-] Error: Ns_PgExec: result status: 7 
message: ERROR:  parser: parse error at or near "to"
 
[06/Oct/2001:18:12:07][711.23560][-conn13-] Error: dbinit: error(localhost::acs,ERROR:  
parser: parse error at or near "to"
): 'select p.product_name, p.product_id, p.dirname, 
p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || 
p.detailed_description || p.search_keywords, 'gi vi') as score
    from ec_products_searchable p
    where pseudo_contains(p.product_name || p.one_line_description ||  p.detailed_description || 
p.search_keywords, 'gi vi') > 0
    order by score desc'
[06/Oct/2001:18:12:07][711.23560][-conn13-] Error: Database operation "select" failed 
(exception NSDB, "Query was not a statement returning rows."), etc., etc...
Collapse
4: Response to Trying to fix pl-sql.sql/pseudo_contains (response to 1)
Posted by Dave Bauer on 10/07/01 01:24 AM
It looks like the old function is still in there:
parse error at or near "to"

I haven't looked at the ecommerce code for 3.x in about 6 months though. Try putting the code for the new function in its own SQL file, drop the function and create the new one.

Collapse
5: Response to Trying to fix pl-sql.sql/pseudo_contains (response to 1)
Posted by Jim Sims on 10/07/01 01:38 AM
Try putting the code for the new function in its own SQL file, drop the function and create the new one.

I think I did that. I fixed the relevant portion of /www/doc/sql/pl-sql.sql, then un-commented the "drop function" line, then sourced the file via:

psql>i /www/doc/sql/pl-sql.sql

DROP

CREATE.

Collapse
6: Response to Trying to fix pl-sql.sql/pseudo_contains (response to 1)
Posted by Jim Sims on 10/07/01 01:45 AM
Nevermind. The patch at https://openacs.org/sdm/one-baf.tcl?baf_id=898 works. Lord knows how I screwed it up; must have pasted it in wrong or something. Time for me to stop working and go play. Thanks and apologies to anyone who put any effort into this.