PLpgSQL function not returning matching titles

Question

I am trying to return the movie name and the number of cast and crew when given a text. When I input the string and am using ilike, my query returns no matching titles. I created a view previously that has the movie titles and the number of crew to be input in the function. My code is:

create or replace view movies_crew as 
select movies.id, movies.title, principals.role
from movies
join principals on principals.movie_id=movies.id 
where principals.role <> 'producer'
;


create or replace view movie_makers as
select movies_crew.title, count(movies_crew.title) as ncrew
from movies_crew
where movies_crew.title = 'Fight Club'
group by movies_crew.title;


CREATE or REPLACE function Q11(partial_title text) 
RETURNS SETOF text
AS $$
    DECLARE
         title text;
    BEGIN
        for title in
            select movie_makers.title, movie_makers.ncrew 
            from movie_makers
            where movie_makers.title ilike '%$1%'
        loop
            return next movie_makers.title||'has'||movie_makers.ncrew||'cast and crew';
        end loop;
        if(not found) then
            return next 'No matching titles';
        end if;

    END;
    
$$ LANGUAGE plpgsql;

select * from q11('Fight Club')

My database is: https://drive.google.com/file/d/1NVRLiYBVbKuiazynx9Egav7c4_VHFEzP/view?usp=sharing

Answer 1

Your immediate quoting issue aside (has been addressed properly by Jeff), the function can be much simpler and faster like this:

CREATE or REPLACE FUNCTION q11(partial_title text) 
  RETURNS SETOF text
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT m.title || ' has ' || m.ncrew || ' cast and crew'
   FROM   movie_makers m
   WHERE  m.title ~* $1;
   
   IF NOT FOUND THEN
      RETURN NEXT 'No matching titles';
   END IF;
END
$func$;

Major points:

• Your function was still broken. References to movie_makers.title and movie_makers.ncrew wouldn't work that way. I fixed it.

• Use RETURN QUERY instead of the loop. This way we also do not need to use or even declare any variables at all. See:

  • How to return result of a SELECT inside a function in PostgreSQL?

• Optionally use the case insensitive regular expression match operator ~*. (Simpler, not faster.)

  • Difference between LIKE and ~ in Postgres

  Either way, you may want to escape special characters. See:

  • Escape function for regular expression or LIKE patterns

Aside: hardly makes sense to filter on a view that already selects 'Fight Club' as its only row. For a meaningful search, you wouldn't use these views ...

Answer 2

ilike '%$1%'

$1 is not interpolated when inside single quotes, so you are searching for the literal characters $ and 1.

You could instead do:

ilike '%'||$1||'%'