Transform Your
PostgreSQL On-Premise Solutions

Use Case:

"Create an intelligent search and analytics system"

Solution:

                        
-- Create necessary extensions and indexes
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE MATERIALIZED VIEW product_search_index AS
WITH product_details AS (
    SELECT 
        p.product_id,
        p.product_name,
        p.description,
        p.price,
        c.category_name,
        ARRAY_AGG(t.tag_name) as tags,
        AVG(r.rating) as avg_rating,
        COUNT(r.rating) as review_count,
        SUM(s.quantity) as total_sales
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.category_id
    LEFT JOIN product_tags pt ON p.product_id = pt.product_id
    LEFT JOIN tags t ON pt.tag_id = t.tag_id
    LEFT JOIN reviews r ON p.product_id = r.product_id
    LEFT JOIN sales s ON p.product_id = s.product_id
    GROUP BY p.product_id, p.product_name, p.description, p.price, c.category_name
)
SELECT 
    product_id,
    product_name,
    description,
    price,
    category_name,
    tags,
    avg_rating,
    review_count,
    total_sales,
    setweight(to_tsvector('english', product_name), 'A') ||
    setweight(to_tsvector('english', category_name), 'B') ||
    setweight(to_tsvector('english', COALESCE(array_to_string(tags, ' '), '')), 'C') ||
    setweight(to_tsvector('english', description), 'D') as document_vector
FROM product_details;

-- Create GIN index for fast text search
CREATE INDEX idx_product_search ON product_search_index USING gin(document_vector);

-- Intelligent search function
CREATE OR REPLACE FUNCTION search_products(
    search_query TEXT,
    min_price DECIMAL = NULL,
    max_price DECIMAL = NULL,
    min_rating DECIMAL = NULL,
    category_filter TEXT = NULL
) RETURNS TABLE (
    product_id INT,
    product_name TEXT,
    category_name TEXT,
    price DECIMAL,
    avg_rating DECIMAL,
    review_count INT,
    search_rank DECIMAL,
    matched_terms TEXT[]
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        psi.product_id,
        psi.product_name,
        psi.category_name,
        psi.price,
        psi.avg_rating,
        psi.review_count,
        ts_rank(document_vector, query) * (1 + ln(COALESCE(review_count, 0) + 1)) as search_rank,
        ARRAY(
            SELECT token 
            FROM ts_debug(search_query) 
            WHERE alias = 'word'
        ) as matched_terms
    FROM 
        product_search_index psi,
        to_tsquery('english', regexp_replace(search_query, '\s+', ' & ', 'g')) query
    WHERE 
        document_vector @@ query
        AND (min_price IS NULL OR price >= min_price)
        AND (max_price IS NULL OR price <= max_price)
        AND (min_rating IS NULL OR avg_rating >= min_rating)
        AND (category_filter IS NULL OR category_name = category_filter)
    ORDER BY search_rank DESC
    LIMIT 50;
END;
$$ LANGUAGE plpgsql;

-- Example usage
SELECT * FROM search_products(
    'organic sustainable food',
    min_price := 10,
    max_price := 100,
    min_rating := 4.0,
    category_filter := 'Organic Foods'
);