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

PostgreSQL - Writing dynamic sql in stored procedure that returns a result set

提问by prince

How can I write a stored procedure that contains a dynamically built SQL statement that returns a result set? Here is my sample code:

如何编写包含返回结果集的动态构建的 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 ;
  ends_with ALIAS FOR ;
  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;

This code returns an error:

此代码返回错误：

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

I have tried other ways instead of this:

我尝试过其他方法而不是这个：

RETURN QUERY EXECUTE sql;

Way 1:

方式一：

RETURN EXECUTE sql;

Way 2:

方式二：

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

In all cases without success.

在所有情况下都没有成功。

Ultimately I want to write a stored procedure that contains a dynamic sql statement and that returns the result set from the dynamic sql statement.

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

回答by Erwin Brandstetter

There is room for improvements:

有改进的空间：

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

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

Major points

要点

• PostgreSQL 8.4 introduced the USINGclause for EXECUTE, which is useful for several reasons. Recap in the manual:

  The command string can use parameter values, which are referenced in the command as $1, $2, etc. These symbols refer to values supplied in the USINGclause. This method is often preferable to inserting data values into the command string as text: it avoids run-time overhead of converting the values to text and back, and it is much less prone to SQL-injection attacks since there is no need for quoting or escaping.

  IOW, it is safer and faster than building a query string with text representation of parameters, even when sanitized with quote_literal().
  Note that $1, $2in the query string refer to the supplied values in the USINGclause, notto the function parameters.

• While you return SELECT * FROM lookups.countries, you can simplify the RETURNdeclaration like demonstrated:

  RETURNS SETOF lookups.countries
  

  In PostgreSQL there is a composite type defined for every table automatically. Use it. The effect is that the function depends on the type and you get an error message if you try to alter the table. Drop & recreate the function in such a case.

  This may or may not be desirable - generally it is! You want to be made aware of side effects if you alter tables. The way you have it, your function would break silently and raise an exception on it's next call.

• If you provide an explicit defaultfor the second parameter in the declaration like demonstrated, you can (but don't have to) simplify the call in case you don't want to set an upper bound with ends_with.

  SELECT * FROM report_get_countries_new('Zaire');
  

  instead of:

  SELECT * FROM report_get_countries_new('Zaire', NULL);
  

  Be aware of function overloadingin this context.

• Don't quote the language name 'plpgsql'even if that's tolerated (for now). It's an identifier.

• You can assign a variable at declaration time. Saves an extra step.

• Parameters are named in the header. Drop the nonsensical lines:

   starts_with ALIAS FOR ;
   ends_with ALIAS FOR ;
  

• PostgreSQL 8.4 引入了USINGfor 子句EXECUTE，这有几个原因。手册中的回顾：

  命令字符串可以使用参数值，这些值在命令中被引用为$1, $2等。这些符号指的是USING子句中提供的值。这种方法通常比将数据值作为文本插入到命令字符串中更可取：它避免了将值转换为文本并返回的运行时开销，并且不太容易受到 SQL 注入攻击，因为不需要引用或逃跑。

  IOW，即使使用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 ;
   ends_with ALIAS FOR ;
  

回答by Frank Heikens

Use quote_literal()to avoid SQL injection(!!!) and fix your quoting problem:

使用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 ;
  ends_with ALIAS FOR ;
  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;

This is tested in version 9.1, works fine.

这是在 9.1 版中测试过的，工作正常。