如何修改 PL/pgSQL 函数以返回动态 SQL 查询的结果？

修改PL/pgSQL函数以返回查询结果

背景

最初，您创建了一个函数，该函数输出格式良好的PostgreSQL SELECT 查询字符串。现在，您希望直接对数据库执行生成的SELECT语句并检索其结果。

方法：动态SQL和返回类型

动态SQL

要执行动态SQL，我们使用EXECUTE命令。由于您没有使用游标，因此您可以使用RETURN QUERY EXECUTE语法。

返回类型

挑战在于返回未定义类型的记录，因为函数需要声明返回类型。我们将根据数据的具体特征考虑各种方法。

使用固定返回类型的简单解决方案

假设使用时间戳、文本和文本列的固定返回类型，我们可以将函数定义如下：

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, col2 text, col3 text)
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora, %s
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _id;
END
$$ LANGUAGE plpgsql;</code>

同类型的可变列数

如果您有可变数量的列，并且所有列的类型相同（例如，双精度），我们可以使用ARRAY类型嵌套值：

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[])
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora
           , string_to_array(, ',')  -- AS names
           , ARRAY[%s]            -- AS values
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _sensors, _id;
END
$$ LANGUAGE plpgsql;</code>

各种完整的表类型

要返回表的全部列，我们可以使用多态类型：

<code class="language-sql">CREATE FUNCTION data_of(_tbl_type anyelement, _id int)
RETURNS SETOF anyelement
AS $$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT *
       FROM   %I  -- pg_typeof returns regtype, quoted automatically
       WHERE  id = 
       ORDER  BY datahora',
      pg_typeof(_tbl_type)
   )
   USING _id;
END
$$ LANGUAGE plpgsql;</code>

注意：第二个例子中，string_to_array 函数需要一个分隔符，这里添加了 , 作为分隔符。 第三个例子使用了 %I 格式化标识符，以更安全的方式处理表名，防止SQL注入。 选择哪种方法取决于您的具体需求和数据结构。 如果可能，提供更多关于您的表结构和所需结果的信息，可以帮助我提供更精确和有效的解决方案。

以上是如何修改 PL/pgSQL 函数以返回动态 SQL 查询的结果？的详细内容。更多信息请关注PHP中文网其他相关文章！