PLpgSQL函数未返回匹配的标题 [英] PLpgSQL function not returning matching titles

问题描述

我正在尝试在收到文本时返回电影名称以及演员和工作人员的数量。当我输入字符串并使用ilike时，我的查询没有返回匹配的标题。我在前面创建了一个视图，其中包含要在函数中输入的电影标题和剧组人数。 我的代码是：

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')

我的数据库是：https://drive.google.com/file/d/1NVRLiYBVbKuiazynx9Egav7c4_VHFEzP/view?usp=sharing

推荐答案

撇开您的即时报价问题不谈(杰夫已经很好地解决了)，该函数可以更简单、更快，如下所示：

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$;

要点：

• 您的功能仍然中断。对movie_makers.title和movie_makers.ncrew的引用不会以这种方式工作。我修好了。

• 使用RETURN QUERY而不是循环。这样，我们根本不需要使用或甚至不需要声明任何变量。请参阅：

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

• 可以选择使用不区分大小写的正则表达式匹配运算符~*。(更简单，而不是更快。)

  • Difference between LIKE and ~ in Postgres

  无论哪种方式，您都可能希望转义特殊字符。请参阅：

  • Escape function for regular expression or LIKE patterns

撇开：在一个已经选择了"Fight Club"作为其唯一行的视图上进行筛选几乎没有意义。对于有意义的搜索，您不会使用这些视图...

这篇关于PLpgSQL函数未返回匹配的标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！