1. 首页
  2. 问题
  3. pl / pgsql函数中动态SQL的语法错误
小编典典

pl / pgsql函数中动态SQL的语法错误

sql

我在PostgreSQL 10中使用pl / pgsql创建复杂的查询。我正在测试一个带有JOINs和ANDs的查询。这是我到目前为止的内容：

DROP FUNCTION IF EXISTS search_person(name text);
CREATE  FUNCTION search_person(name text) RETURNS TABLE(address_id integer, address_geom text, event_name text) AS $$
--DECLARE

BEGIN
    RETURN QUERY EXECUTE 
    'SELECT address.id, event.name, address.geom 
    FROM  event JOIN person JOIN address JOIN person_address JOIN event_person
    WHERE 
    person_address.event_id = event.id AND
    event_person.event_id = event.id AND
    person.id = event_person.person_id AND
    person.name like
    $1'

    USING name;
END;
$$
LANGUAGE plpgsql;

创建此函数时，我没有任何错误。我这样称呼select search_person('nick');，我得到：

ERROR:  syntax error at or near "WHERE"
LINE 3:     WHERE
            ^
QUERY:  SELECT address.id, event.name, address.geom
    FROM  event JOIN person JOIN address JOIN person_address JOIN

event_person
WHERE
person_address.event_id = event.id AND
event_person.event_id = event.id AND
person.id = event_person.person_id AND
person.name like
$1
CONTEXT: PL/pgSQL function search_creator(text) line 5 at RETURN QUERY
SQL state: 42601

我看不到或解决该问题。我尝试在子句中替换AND为，但没有任何更改。||``WHERE

我该怎么办？

编辑

这是我现在拥有的代码，根据我检查的数据库数据，即使应该得到结果，我也会得到一个空表。

CREATE  FUNCTION search_person(name character(600)) RETURNS TABLE(address_id bigint, address_geom geometry, event_name character(200)) AS $$

BEGIN
    RETURN QUERY EXECUTE 
    'SELECT address.id, address.geom, event.name

    FROM
    person 
    JOIN event_creator ON event_person.person_id = person.id
    JOIN event ON event.id = event_person.event_id 
    JOIN person_address ON person_address.event_id = event.id 
    JOIN address ON address.id = cep.address_id

    WHERE person.name LIKE $1'
    USING name;

END;
$$
LANGUAGE plpgsql;

阅读 216

收藏
2021-03-17

共1个答案

小编典典

当创建一个PL / pgSQL函数，函数体被保存为字符串字面 原样 。仅应用表面语法检查。所包含的语句实际上并未在更深层次上执行或测试。

但是
，在实际的SQL语句中仍会检测到查询字符串中类似的基本语法错误。但是，您正在使用带有动态SQLEXECUTE。该语句包含在嵌套字符串文字中，仅由您自己负责。

首先，这似乎被误导了。没有明显的理由可以使用动态SQL。 （除非您的数据分布非常不均匀，并且要强制Postgres为每个输入值生成一个自定义计划。）

如果使用普通的SQL语句，则在创建时会收到错误消息：

CREATE OR REPLACE FUNCTION search_person(name text)  -- still incorrect!
  RETURNS TABLE(address_id integer, address_geom text, event_name text) AS
$func$
BEGIN
   RETURN QUERY
   SELECT address.id, event.name, address.geom 
   FROM  event JOIN person JOIN address JOIN person_address JOIN event_person
   WHERE 
   person_address.event_id = event.id AND
   event_person.event_id = event.id AND
   person.id = event_person.person_id AND
   person.name like $1;  -- still $1, but refers to func param now!
END
$func$  LANGUAGE plpgsql;

SQL语句仍然无效。 需要
一个连接条件-如尼克所说。而且我完全看不到需要PL /
pgSQL。一个简单的 SQL函数* 应该可以很好地 发挥作用 ：[INNER] JOIN __*

CREATE FUNCTION search_person(name text)
  RETURNS TABLE(address_id integer, address_geom text, event_name text) AS
$func$
   SELECT a.id, a.geom, e.name  -- also fixed column order to match return type
   FROM   person         AS p
   JOIN   event_person   AS ep ON ep.person_id = p.id
   JOIN   event          AS e  ON e.id = ep.event_id
   JOIN   person_address AS pa ON pa.event_id = e.id
   JOIN   address        AS a  ON a.id = pa.address_id -- missing join condition !!
   WHERE  p.name LIKE $1;
$func$  LANGUAGE sql;

我使用表别名重写查询以修复语法错误，以提高可读性。最后，我还根据有根据的猜测又添加了一个失踪条件：a.id = pa.address_id。

现在应该可以了。

有关的：

或根本没有功能，只需使用 准备好的语句即可 。例子：

如果您毕竟需要动态SQL，请像使用它一样通过子句传递 值 ，USING并确保在 连接 查询时防止SQL注入。Postgres提供了各种工具：

2021-03-17