pl/pgsql函数中动态SQL的语法错误 [英] Syntax error in dynamic SQL in pl/pgsql function

问题描述

我在PostgreSQL 10中使用pl/pgsql创建复杂的查询.我正在测试几个JOIN和AND的查询.这是我到目前为止的内容:

I am using pl/pgsql in PostgreSQL 10, to create complex queries. I am testing a query with a couple of JOINs and ANDs. This is what I have so far:

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');我得到:

I get no errors while creating this function. I call it like so select search_person('nick'); and I get:

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

我看不到或解决该问题.我尝试在WHERE子句中用||替换AND，但没有任何变化.

I cannot see or fix the problem. I tried replacing AND with || in the WHERE clause, but nothing changed.

我该怎么办?

编辑

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

This is the code I have now and I get an empty table, even though I should get results, according to my database data that I checked.

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;

推荐答案

创建PL/pgSQL函数时，函数主体按原样保存为字符串常量 .仅应用表面语法检查.包含的语句实际上并未在更深层次上执行或测试.

When creating a PL/pgSQL function, the function body is saved as string literal as is. Only superficial syntax checks are applied. Contained statements are not actually executed or tested on a deeper level.

但是，在实际的SQL语句中仍会检测到查询字符串中的基本语法错误.但是您正在使用动态SQL和EXECUTE .该语句包含在一个嵌套的字符串文字中，这是您的责任.

However, basic syntax errors like you have in your query string would still be detected in actual SQL statements. But you are using dynamic SQL with EXECUTE. The statement is contained in a nested string literal and is your responsibility alone.

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

This seems to be misguided to begin with. There is no apparent reason for dynamic SQL. (Unless you have very uneven data distribution and want to force Postgres to generate a custom plan for each input value.)

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

If you had used a plain SQL statement, you would have gotten the error message at creation time:

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语句仍然无效. [INNER] JOIN 需要加入条件-像尼克一样评论 .而且我完全看不到需要PL/pgSQL.一个简单的 SQL函数应该可以很好地发挥作用:

The SQL statement is still invalid. [INNER] JOIN requires a join condition - like Nick commented. And I don't see the need for PL/pgSQL at all. A simple SQL function should serve well:

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.

I rewrote the query to fix syntax error, using table aliases for better readability. Finally, I also added one more missing condition based on an educated guess: a.id = pa.address_id.

现在应该可以了.

相关:

• plpgsql函数未按预期插入数据
• 语言sql和PostgreSQL函数中的语言plpgsql

• plpgsql function not inserting data as intended
• Difference between language sql and language plpgsql in PostgreSQL functions

或者根本没有功能，只需使用准备好的语句即可.示例:

Or no function at all, just use a prepared statement instead. Example:

• 拆分给定的字符串并准备案例说明

如果您毕竟需要动态SQL，请像您一样通过USING子句传递 values ，并确保在 concatencate 查询时防止SQL注入. Postgres提供了各种工具:

If you need dynamic SQL after all, pass values with the USING clause like you had it and make sure to defend against SQL injection when concatenating queries. Postgres provides various tools:

• Postgres函数中的SQL注入与准备查询
• 定义表和列名称作为plpgsql函数中的参数?
• 表名作为PostgreSQL函数参数

• SQL injection in Postgres functions vs prepared queries
• Define table and column names as arguments in a plpgsql function?
• Table name as a PostgreSQL function parameter

这篇关于pl/pgsql函数中动态SQL的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！