在pl/pgsql中使用文本会带来空结果 [英] Using text in pl/pgsql brings empty set of results

问题描述

我是pl/pgsql的新手，正在尝试创建动态查询.我现在所拥有的是用于测试的参数的基本组合.当它正常运行时，我将逐渐添加更多动态部件，以创建一个动态的多合一查询.

I am new to pl/pgsql and trying to create a dynamic query. What I have now is a basic combination of parameters for testing. When it works properly, I will gradually add more dynamic parts, to create a dynamic, all-in-one query.

问题是这应该可以工作，但是我在pgadmin4中看到一个空的search_creator作为数据输出.这是代码

The problem is that this should work, but instead I see an empty search_creator as Data Output in the pgadmin4. This is the code

CREATE  FUNCTION search_creator(creator text)
  RETURNS TABLE(place_id bigint, place_geom geometry, event_name character(200)) AS
$$
BEGIN
    RETURN QUERY EXECUTE 
    'SELECT place.id, place.geom, event.name
     FROM person
     JOIN event_creator ON event_creator.person_id = person.id
     JOIN event ON event.id = event_creator.event_id
     JOIN cep ON cep.event_id = event.id
     JOIN place ON place.id = cep.place_id
     WHERE person.name LIKE $1'
    USING creator;
END;
$$
LANGUAGE plpgsql;

这就是我调用函数select search_creator('mike');的方式.

This is how I call the function select search_creator('mike');.

如果有帮助，在数据库中，person.id列的类型为character(200).

If it helps, in the database, the person.id column is type character(200).

如果有帮助，当我修改函数以接受int作为输入并将WHERE部分更改为WHERE person.id = $1时，则可以正常工作.我可以在pgadmin输出中看到实际结果.

If it helps, when I modify the function to accept int as input and alter the WHERE part to WHERE person.id = $1 , then it works fine. I can see actual results in the pgadmin output.

我的文本变量有什么问题?是语法吗?

What is wrong with my text variables? Is it the syntax?

另外，我该怎么做类似WHERE person.name LIKE '%$1%'的事情?

Also, how can I do something like WHERE person.name LIKE '%$1%'?

推荐答案

如果您运行

SELECT search_creator('mike');

该函数将执行

SELECT ... WHERE person.name LIKE 'mike'

与

SELECT ... WHERE person.name = 'mike'

显然没有这样的记录.

要添加和添加%，您可以使用

To prepend and append %, you could use

EXECUTE 'SELECT ...
         WHERE person.name LIKE ' || quote_nullable('%' || creator || '%'); 

这篇关于在pl/pgsql中使用文本会带来空结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！