PostgreSQL - 编写返回结果集的存储过程中的动态SQL

Question

PostgreSQL - 编写返回结果集的存储过程中的动态SQL

postgresqlresultsetplpgsqlexecutedynamic-sql

14

我如何编写一个包含动态构建 SQL 语句返回结果集的存储过程？这是我的示例代码：

CREATE OR REPLACE FUNCTION reporting.report_get_countries_new (
  starts_with varchar,
  ends_with varchar
)
RETURNS TABLE (
  country_id integer,
  country_name varchar
) AS
$body$
DECLARE
  starts_with ALIAS FOR $1;
  ends_with ALIAS FOR $2;
  sql VARCHAR;
BEGIN

    sql = 'SELECT * FROM lookups.countries WHERE lookups.countries.country_name >= ' || starts_with ;

    IF ends_with IS NOT NULL THEN
        sql = sql || ' AND lookups.countries.country_name <= ' || ends_with ;
    END IF;

    RETURN QUERY EXECUTE sql;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

这段代码返回一个错误：

ERROR:  syntax error at or near "RETURN"
LINE 1: RETURN QUERY SELECT * FROM omnipay_lookups.countries WHERE o...
        ^
QUERY:  RETURN QUERY SELECT * FROM omnipay_lookups.countries WHERE omnipay_lookups.countries.country_name >= r
CONTEXT:  PL/pgSQL function "report_get_countries_new" line 14 at EXECUTE statement

除了这种方式，我已经尝试过其他方法：

RETURN QUERY EXECUTE sql;

方法1：

RETURN EXECUTE sql;

第二种方法：

sql = 'RETURN QUERY SELECT * FROM....
/*later*/
EXECUTE sql;

所有尝试都没有成功。

最终我想编写一个包含动态SQL语句并返回该语句结果集的存储过程。

- prince

这个问题是错误的。你在问题中提到了存储过程，但你在这里编写的代码是关于函数的... - Nemo

2个回答

7

使用 quote_literal() 来避免 SQL注入（!!!）并解决引用问题：

CREATE OR REPLACE FUNCTION report_get_countries_new (
  starts_with varchar,
  ends_with varchar
)
RETURNS TABLE (
  country_id integer,
  country_name varchar
) AS
$body$
DECLARE
  starts_with ALIAS FOR $1;
  ends_with ALIAS FOR $2;
  sql VARCHAR;
BEGIN

    sql := 'SELECT * FROM lookups.countries WHERE lookups.countries.country_name ' || quote_literal(starts_with) ;

    IF ends_with IS NOT NULL THEN
        sql := sql || ' AND lookups.countries.country_name <= ' || quote_literal(ends_with) ;
    END IF;

    RETURN QUERY EXECUTE sql;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

这已经在9.1版本进行了测试，运行良好。

- Frank Heikens

缺少引用是问题所在。返回的错误信息具有误导性，使我认为 SQL 语句不受支持（在 EXECUTE 之前使用 RETURN QUERY），而不是它写得有问题。感谢 quote_literal 提示。输入数据正在 PHP 层面进行纠正。 - prince

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

还有改进的空间：

CREATE OR REPLACE FUNCTION report_get_countries_new (starts_with text
                                                   , ends_with   text <b>= NULL</b>)
  <b>RETURNS SETOF lookups.countries</b> AS
$func$
DECLARE
   sql text := 'SELECT * FROM lookups.countries WHERE country_name >= <b>$1</b>';
BEGIN
   IF ends_with IS NOT NULL THEN
      sql := sql || ' AND country_name <= <b>$2</b>';
   END IF;

   RETURN QUERY EXECUTE sql
   <b>USING starts_with, ends_with</b>;
END
$func$ LANGUAGE plpgsql;
-- the rest is default settings

主要要点

PostgreSQL 8.4引入了EXECUTE的USING子句，有几个好处。手册中的回顾：

命令字符串可以使用参数值，在命令中用$1、$2等引用这些符号则对应于在USING子句中提供的值。这种方法通常比将数据值作为文本插入到命令字符串中更可取：它避免了将值转换为文本和返回的运行时开销，并且由于不需要引号或转义，因此 SQL 注入攻击的风险大大降低。

也就是说，即使经过quote_literal()的清理，使用文本表示参数构建查询字符串仍然存在安全与效率问题。
请注意，查询字符串中的$1、$2引用的是提供给USING子句的值，而不是函数参数。
当您返回SELECT * FROM lookups.countries时，可以像演示的那样简化RETURN声明：
```
RETURNS SETOF lookups.countries
```
在PostgreSQL中，每个表都会自动定义一个复合类型。使用它。这样做的效果是函数取决于类型，如果尝试修改表格，则会出现错误消息。在这种情况下，请删除和重建该函数。

这可能是可取的（或不可取） - 通常是可取的！您希望在更改表时了解副作用。如果您按照现有方式进行，函数将会默默地失败并在下一次调用时引发异常。

如果在声明中为第二个参数提供明确的默认值，就像所示的那样，您可以（但不必）简化调用，以防您不想使用ends_with设置上限。
```
SELECT * FROM report_get_countries_new('Zaire');
```
改为：
```
SELECT * FROM report_get_countries_new('Zaire', NULL);
```
在这个上下文中要注意函数重载。
即使当前可以接受，也不要引用语言名称'plpgsql'。它是一个标识符。
你可以在声明时分配变量。这样可以省去一步操作。

参数在头部命名。删除无意义的行：

 starts_with ALIAS FOR $1;
 ends_with ALIAS FOR $2;