PostgreSQL-在存储过程中编写动态sql并返回结果集 [英] PostgreSQL - Writing dynamic sql in stored procedure that returns a result set

问题描述

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

 创建或替换功能report.report_get_countries_new（
 starts_with varchar，
ends_with varchar 
）
 returns table（
 country_id整数，
 country_name varchar 
）AS 
 $ body $ 
 DECLARE 
 starts_with ALIAS为$ 1; 
 ends_with ALIAS FOR $ 2; 
 sql VARCHAR; 
开始
 
 sql =‘SELECT * FROM lookups.countries WHERE lookups.countries.country_name> =’||以。。开始 ; 
 
如果end_with不为空，那么
 sql = sql || ’AND lookups.countries.country_name< =’||以。。结束 ; 
 END IF; 
 
返回查询执行sql; 
 
 END； 
 $ body $ 
语言‘plpgsql’
波动性
调用空输入
安全调用程序
费用为100列； 
  

此代码返回错误：

 错误：返回或附近的语法错误
第1行：返回查询选择*来自omnipay_lookups.countries在... 
 ^ 
查询：返回查询选择*从omnipay_lookups.countries，在omnipay_lookups.countries.country_name> = r 
上下文：EXECUTE语句
   
 
 
我尝试了其他替代方法：
 返回查询执行sql; 
  
方法1：
 返回执行sql; 
  
方法2：
  sql ='返回查询选择*从.... 
 / *以后* / 
执行sql; 
  
在所有情况下均未成功。
 
 
 
最终，我想编写一个包含动态sql语句并从动态sql语句返回结果集的存储过程。
解决方案
还有改进的空间：
 创建或替换功能report_get_countries_new（以文本
开始，以文本 =结尾NULL ）
 返回SETUP lookups.countries  AS 
 $ func $ 
 DECLARE 
 sql文本：='SELECT * FROM lookups.countries WHERE country_name> =  $ 1 '; 
开始
如果end_with不为空，那么
 sql：= sql || ’AND country_name< =  $ 2 ; 
 END IF; 
 
返回查询执行sql 
 使用starts_with，ends_with ; 
 END 
 $ func $语言plpgsql; 
-其余为默认设置
  
 
 
 
要点
 
 
 
 
 
 
 PostgreSQL 8.4引入了  USING  子句用于 EXECUTE ，出于多种原因，它很有用。 手册中的内容回顾：
 
 
命令字符串可以使用参数值，这些值在
命令中以 $ 1，$ 2 等。这些符号表示子句在
中提供的值。此方法通常比将数据
的值作为文本插入命令字符串更可取：它避免了
的运行时开销，即将值转换为文本并返回的情况，并且不太容易出现
由于不需要引号或转义符，因此可以进行SQL注入攻击。
 
 
 IOW，它比使用以下命令构建查询字符串更安全，更快捷参数的文本表示形式，即使使用 quote_literal（）进行了消毒。
 
请注意， $ 1，$ 2 在查询字符串中，请参考 USING 子句中提供的值，而 not 则是函数参数。
 
 
 
 
当您返回 SELECT * FROM lookups.countries 时，您可以简化  RETURN  声明如显示：
 返回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; 
  
 
 
 
 
How can I write a stored procedure that contains a dynamically built SQL statement that returns a result set?  Here is my sample code:
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;
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.
解决方案
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 >= $1';
BEGIN
   IF ends_with IS NOT NULL THEN
      sql := sql || ' AND country_name <= $2';
   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 USING clause 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 USING clause. 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, $2 in the query string refer to the supplied values in the USING clause, not to the function parameters.
While you return SELECT * FROM lookups.countries, you can simplify the RETURN declaration 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 default for 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 overloading in 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 $1;
 ends_with ALIAS FOR $2;



                        
这篇关于PostgreSQL-在存储过程中编写动态sql并返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！