Answer for Seven Databases in Seven Weeks Day 3 Do#1

search_movies.sql

/**
 * Seven Databases in Seven Weeks: Day 3 Do#1
 *
 * Create a stored procedure where you can input a movie title or actor’s name
 * you like, and it will return the top five suggestions based on either movies
 * the actor has starred in or films with similar genres.
 */

CREATE OR REPLACE FUNCTION search_movies(term text) RETURNS SETOF movies AS $$
DECLARE
    aid integer; -- actor_id
    m movies%ROWTYPE;
    r RECORD;
    enlarge integer := 0;
    count integer := 0;
    num_results CONSTANT integer := 5;
    cube_query text;
BEGIN
    SELECT actor_id INTO aid FROM actors WHERE lower(name) = lower(term);
    IF FOUND THEN
        /**
         * search term is actor name, return movies the actor is in
         */
        RETURN QUERY SELECT m_tbl.* FROM movies AS m_tbl INNER JOIN movies_actors AS ma_tbl ON m_tbl.movie_id = ma_tbl.movie_id WHERE ma_tbl.actor_id = aid LIMIT num_results;
    ELSE
        SELECT * INTO m FROM movies WHERE lower(title) = lower(term);
        IF FOUND THEN
            /**
             * search term is a movie title, return similar movies
             */
            WHILE count < num_results LOOP
                cube_query = 'SELECT * FROM movies WHERE movie_id <> $1 AND cube_enlarge($2, $3, 18) @> genre';
                IF enlarge > 0 THEN
                    cube_query = cube_query || ' AND movie_id NOT IN (SELECT movie_id FROM movies WHERE movie_id <> $1 AND cube_enlarge($2, $3 - 1, 18) @> genre)'; -- exclude the results from previous iteration
                END IF;
                cube_query = cube_query || ' LIMIT $4';
                
                FOR r IN EXECUTE cube_query USING m.movie_id, m.genre, enlarge, num_results
                LOOP
                    EXIT WHEN count = num_results;
                    count = count + 1;
                    RETURN NEXT r;
                END LOOP;
                
                enlarge = enlarge + 1;
            END LOOP;
        ELSE
            RAISE 'result not found: %', term;
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;