I have written a simple search engine that utilizes pgsql. I would
like to make it a stored procedure, although I am still learning
plpgsql. Attached to this message is the basic search logic (in PHP).
This script will be in production on a major e-commerce site in about
a week. I think it would be much faster as a stored procedure.
This implemetation searches product data, but it could be used for
anything.
The script does a few things, gets the id's of valid words, finds
which products these words are mapped to, finds which products have
the most keyword mappings out of the result set, and outputs a result
set joined to the product table and ordered by products with the most
keyword hits.
It accomplishes this by keeping an indexed list of words, maintaing a
mapping table to products. Then when someone does a search, a
temporary table called 'hits' is created which stores the product_id
that was matched to a word. Then an additional temporary table is
created which consists of the product id and hit count from the
search. The search then retrns the product details ordered by the
product that had the most hits.
If you are interested in seeing it in action, I can send you a url, if
you'd like to implement it I can help you out, and if you can help me
covert it to a stored procedure I'd be very apreciative!
It uses five tables:
CREATE TABLE "pa_search_keyword" ( "keyword_id" int4 DEFAULT
nextval('"pa_search_keywor_keyword_id_seq"'::text) NOT NULL, "keyword_value" varchar(30), CONSTRAINT
"pa_search_keyword_pkey"PRIMARY KEY ("keyword_id")
);
and
CREATE TABLE "pa_search_map" ( "keyword_id" int4 NOT NULL, "product_id" int4 NOT NULL, CONSTRAINT
"pa_search_map_pkey"PRIMARY KEY ("keyword_id",
"product_id")
);
two temporary tables:
CREATE TEMPORARY TABLE hits
(product_id integer not null);
and
CREATE TEMPORARY TABLE prod_hit_count (
product_id integer not null,
hit_count smallint not null
);
the fifth table would be the table you are joining to to get the
product data, or details, or whatever.
-Ryan Mahoney