/*------------------------------- Scholastic High-tech Internet Teachers Store Web Database Functions (Stored Procedures) Revision Log --------------------------------------------------------------------------- Who Date Comments ====== =========== ====================================================== Alan 08 Aug 2006 Initial pass Alan 10 Aug 2006 Small modification to the parameters. Alan 11 Aug 2006 Small modification to the cursors and returned columns Added Age Range searches Template ---------------------------------------------------- create or replace function fnName(refcursor) returns refcursor as $$ declare -- begin open $1 for select * from someTable; return $1; end; $$ language plpgsql; Then test with: begin; select fnName('data'); fetch all in data; commit; IMPORTANT NOTE: --------------- Note the differences between ACTIVE and CURRENT in these functions. ACTIVE simply means that product.is_active == true. CURRENT means that the product is present in the view of current_offers. They will be different. -------------------------------*/ -- Connect to the server and use the ScholasticWeb database. -- Set the Schema search path. -- set search_path to 'ECM'; /* TODO: - DONE - Find products by subject category - DONE - Find by age range (containment and overlap) - "Search Shop" - Find offers by title, isbn, short_description, long_description, search_text - Find products feature / theme - Find related items select product_id, count(feature_id) as "common_features" from features_products where feature_id in (select feature_id from features_products where product_id = 2) group by product_id order by common_features desc */ /*-------------------------------------------------------------------------- Find Active Products by Subject Category - Exact (but NOT CASE SENSITIVE) --------------------------------------------------------------------------*/ create or replace function search_by_subject_exact(text, refcursor) returns refcursor as $$ declare searchexpr varchar := $1; resultset refcursor := $2; begin open resultset for select sc.title as "subject category", p.* from subject_categories sc , products p where p.subject_category_id = sc.id and upper(sc.title) = upper(searchexpr) and p.is_active ; return resultset; end; $$ language plpgsql; -- To execute: -- SELECT search_by_subject_exact('search text', 'results'); -- FETCH ALL FROM results; /*--------------------------------------------------------------------------- Find Active Products by Subject Category - Fuzzy (sill NOT CASE SENSITIVE) ---------------------------------------------------------------------------*/ create or replace function search_by_subject_fuzzy(text, refcursor) returns refcursor as $$ declare searchexpr varchar := $1; resultset refcursor := $2; begin searchexpr := '%' || searchexpr || '%'; open resultset for select sc.title as "subject category", p.* from subject_categories sc , products p where p.subject_category_id = sc.id and upper(sc.title) like upper(searchexpr) and p.is_active ; return resultset; end; $$ language plpgsql; /*---------------------------------------------- Find Current Products by CONTAINED Age Range ----------------------------------------------*/ create or replace function search_by_containing_age_range(integer, integer, refcursor) returns refcursor as $$ declare range_start integer := $1; range_end integer := $2; resultset refcursor := $3; begin open resultset for select p.* from products p , items i where p.is_active and p.id = i.id and i.age_start >= range_start and i.age_end <= range_end and p.id in (select product_id from current_offers) ; return resultset; end; $$ language plpgsql; /*------------------------------------------------ Find Current Products by OVERLAPPING Age Range ------------------------------------------------*/ create or replace function search_by_overlapping_age_range(integer, integer, refcursor) returns refcursor as $$ declare range_start integer := $1; range_end integer := $2; resultset refcursor := $3; begin open resultset for select p.* from products p , items i where p.is_active and p.id in (select product_id from current_offers) and p.id = i.id and ( (i.age_start between range_start and range_end) or (i.age_end between range_start and range_end) ) ; return resultset; end; $$ language plpgsql;